Zeer trage query wat doe ik fout?

[Voxyn]

Hallo mensen ik ben bezig met een query te schrijven die de nieuwste prijzen van alle producten moet ophalen(prijs wisselt iedere dag en soms vaker) alleen de query is zeer traag met ~20.000 records doet hij er al 6 secondes over en hij zal in de toekomst toch wel iets van 1 miljoen records krijgen.

de tabel ziet er als volgt uit:
tabel: product_prijs
product_id int(11)
prijs decimal(8,4)
datum timestamp (default value: current_timestamp)


en de query:

code:

select pp.product_id,pp.prijs,pp.datum 
from product_prijs pp
where p.datum = (select max(datum) from product_prijs where pp.product_id = product_id)

heeft iemand enig idee hoe dit sneller kan?

[ Voor 8% gewijzigd door Voxyn op 06-04-2012 10:12 ]

[Cyphax]

Kijk eens naar het execution plan (met EXPLAIN, daar kun je de bottlenecks mee vinden.
Wat heb je aan indexen liggen op de tabellen?

Welk DBMS is dit? MySQL lijkt het?

[ Voor 34% gewijzigd door Cyphax op 06-04-2012 09:44 ]

[EfBe]

Per row in product_prijs executeert hij een subquery die een max aggregate uitvoert (en dus ALLE rows leest) over tankstations_brandstoffen_prijs. Dat is dus een n*m query.

Plaats in ieder geval een index op datum en een index op product_id in tankstations_brandstoffen_prijs

[Voxyn]

Cyphax schreef op vrijdag 06 april 2012 @ 09:42:
Kijk eens naar het execution plan, daar kun je de bottlenecks mee vinden.
Wat heb je aan indexen liggen op de tabellen?

Hoe kan ik de execution plan bekijken?

en phpmyadmin geeft de volgende gegevens bij de tabel aan
sleutelnaam: fk_product_prijs_producten type: BTREE, unieke waarde: nee, gecomprimeerd:nee, kolom: product_id,datum, kardinaliteit: 400,20000, collatie: A,A

EfBe schreef op vrijdag 06 april 2012 @ 09:44:
Per row in product_prijs executeert hij een subquery die een max aggregate uitvoert (en dus ALLE rows leest) over tankstations_brandstoffen_prijs. Dat is dus een n*m query.

Plaats in ieder geval een index op datum en een index op product_id in tankstations_brandstoffen_prijs

ik heb geloof een index die en product_id en datum doet is verkeerd of moet ik er twee hebben een voor product_id en een voor datum?

[ Voor 34% gewijzigd door Voxyn op 06-04-2012 09:52 ]

[Meekoh]

Veel query's vallen of staan door het gebruik van de juiste indexen.
Ik heb queries gezien die na het toevoegen van een bepaalde index van 10s naar 1s of lager gingen.

[Cyphax]

Voxyn schreef op vrijdag 06 april 2012 @ 09:47:
[...]

Hoe kan ik de execution plan bekijken?

en phpmyadmin geeft de volgende gegevens bij de tabel aan
sleutelnaam: fk_product_prijs_producten type: BTREE, unieke waarde: nee, gecomprimeerd:nee, kolom: product_id,datum, kardinaliteit: 400,20000, collatie: A,A

Ik heb na een edit een linkje toegevoegd naar de documentatie van MySQL waar dat in staat.
Hier is nog een linkje met uitleg van een andere bron, misschien leest die prettiger: http://www.devshed.com/c/...s-for-Speed-with-EXPLAIN/

[MSalters]

Voxyn schreef op vrijdag 06 april 2012 @ 09:47:
ik heb geloof een index die en product_id en datum doet is verkeerd of moet ik er twee hebben een voor product_id en een voor datum?

Voor deze query wil je het liefste een index op product_id zonder datum. Dan is er geen twijfel over dat die gebruikt kan worden voor de subquery.

[cariolive23]

En wat als je de subquery verplaatst naar een JOIN?

SQL:

SELECT 
  pp.product_id,pp.prijs,pp.datum 
FROM
  product_prijs pp
    JOIN (SELECT product_id, MAX(datum) m_datum FROM product_prijs GROUP BY product_id) sub
      ON pp.product_id = sub.product_id AND pp.datum = sub.m_datum;

Een gecombineerde index op product_id en datum is voor deze query het meest ideaal. Zie de resultaten van EXPLAIN voor de verschillen met de oorspronkelijke query.

[GlowMouse]

zo is hij het snelste:

SQL:

select pp.product_id,pp.prijs,pp.datum 
from product_prijs pp
where not exists(select 1 from product_prijs where pp.product_id = product_id and pp.datum < datum)

met de index die je al hebt (op (product_id,datum)).

Meekoh schreef op vrijdag 06 april 2012 @ 09:51:
Veel query's vallen of staan door het gebruik van de juiste indexen.
Ik heb queries gezien die na het toevoegen van een bepaalde index van 10s naar 1s of lager gingen.

over open deuren gesproken

MSalters schreef op vrijdag 06 april 2012 @ 11:36:
[...]

Voor deze query wil je het liefste een index op product_id zonder datum. Dan is er geen twijfel over dat die gebruikt kan worden voor de subquery.

waarom twijfel je?

[cariolive23]

GlowMouse schreef op zaterdag 07 april 2012 @ 16:59:
zo is hij het snelste:

Gebruik dan wel EXISTS() i.p.v. NOT EXISTS()...

En meten = weten, het hoeft echt niet sneller te zijn dan een JOIN, daar is geen garantie op te geven.

[DukeBox]

Soms is het sneller om een max waarde in PHP te bepalen i.p.v. MySQL. (max($array)). Een beetje afhankelijk van je dataset en verbinding tussen je webserver en db.

[GlowMouse]

cariolive23 schreef op zaterdag 07 april 2012 @ 20:15:
[...]

Gebruik dan wel EXISTS() i.p.v. NOT EXISTS()...

Nee, ik bedoel NOT EXISTS.

En meten = weten, het hoeft echt niet sneller te zijn dan een JOIN, daar is geen garantie op te geven.

Gebruik van een join vs. not exists is nauwelijks relevant.

[cariolive23]

GlowMouse schreef op zondag 08 april 2012 @ 12:31:
[...]

Nee, ik bedoel NOT EXISTS.

[...]

Ah, ik had de < datum compleet gemist!

[Verwijderd]

Voxyn schreef op vrijdag 06 april 2012 @ 09:47:
[...]

Hoe kan ik de execution plan bekijken?

en phpmyadmin geeft de volgende gegevens bij de tabel aan
sleutelnaam: fk_product_prijs_producten type: BTREE, unieke waarde: nee, gecomprimeerd:nee, kolom: product_id,datum, kardinaliteit: 400,20000, collatie: A,A


[...]

Staat net vandaag iets over te lezen op Reddit.