[MySQL] query optimaliseerbaar?

[Rekcor]

Ik heb twee tabellen:

orders: ID, klantId, deadline
orderregels: orderId, productId, aantal

Orders bevat 10.000 rijen, orderregels ongeveer 300.000

Nu draai ik de volgende query:

SQL:

EXPLAIN SELECT SUM(aantal) FROM orderregels LEFT JOIN orders ON (orders.ID=orderregels.orderId)
WHERE orders.klantId IN (1, 2, 3, .... 1200)
AND orders.deadline<'2010-01-01'
AND orders.deadline>'2011-12-31'
AND orderregels.productId IN (1, 2, 3, ... 400)

(de ... geven aan dat de rij doorloopt, dus van 1 t/m 1200 en van 1 t/m 400)

En krijg ik als output:

id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	orderregels	ALL	orderId,productId,orderId_2	NULL	NULL	NULL	290822	Using where
1	SIMPLE	orders	ref	PRIMARY,klantId,ID	ID	4	orderregels.orderId	1	Using where; Using index

M.a.w. deze query is langzaaaaaaam. Maar nu de vraag: hoe kan ik hem optimaliseren? Ik heb al een aantal indices gemaakt voor orderregels: orderId, productId en orderId_2 (=orderId+productId), maar hij blijft als key NULL geven.

[Fish]

waarom doe je ook geen klantid <1201 ? en product id < 401

[Voutloos]

Niet de primaire oplossing, maar wel leuk om ook aan te denken: Het lijkt mij dat het resultaat er best een paar naast mag zitten, en dat het resultaat enige tijd gecached kan worden. Of je nou elke keer opnieuw aantal=513.519 berekent of een paar minuten (of whatever) aantal=513.500 cached boeit je eindgebruiker - afhankelijk van het gebruik - misschien niet.

Nog een alternatief is het redundant opslaan van het totale aantal per order, maar dat helpt enkel als je niet meer per product filtert.

[ Voor 21% gewijzigd door Voutloos op 02-08-2011 21:11 ]

[Wolfboy]

Wat versta je onder "heel langzaam"? De query moet siwoeso door 300,000 rijen heenlopen... dus heel snel gaat het natuurlijk ook weer niet worden.

Aangezien dergelijke systemen meestal meer reads dan writes zullen doen zou ik persoonlijk gewoon de `SUM(aantal)` denormaliseren en opslaan in de orders tabel.

[Rekcor]

fish schreef op dinsdag 02 augustus 2011 @ 21:03:
waarom doe je ook geen klantid <1201 ? en product id < 401

In werkelijkheid is het een random verzameling klanten/producten.

[ Voor 3% gewijzigd door Rekcor op 02-08-2011 21:14 ]

[Rekcor]

Wolfboy schreef op dinsdag 02 augustus 2011 @ 21:11:
Wat versta je onder "heel langzaam"? De query moet siwoeso door 300,000 rijen heenlopen... dus heel snel gaat het natuurlijk ook weer niet worden.

25 sec

Aangezien dergelijke systemen meestal meer reads dan writes zullen doen zou ik persoonlijk gewoon de `SUM(aantal)` denormaliseren en opslaan in de orders tabel.

Jouw SQL-skills overstijgen de mijne. Wat bedoel je met `SUM(aantal)` denormaliseren?

[MueR]

Stel, je hebt een tabel "Orders" en een tabel "OrderLines". In plaats van steeds een COUNT() te doen op het aantal OrderLines voor een Order, sla je dat getal op bij een order. Scheelt een subquery oid.

[LazySod]

Waarom een left join van orderregels naar orders? Het lijkt mij dat een orderregel altijd een bijhorende order moet hebben - anders heb je ergens een gat in je datastructuur.

Het lijkt mij dan ook logischer om de insteek te veranderen in:

SQL:

SELECT SUM(orderregels.aantal) 
   FROM orders
   JOIN orderregels ON orderregels.orderid = orders.id AND orderregels.productid in ( ... bla ... )
WHERE orders.klantid in ( ... bla ... )

De indexes die je gemaakt heb (1 op orders.klantid en 1 op orderregels.orderid, orders.productid) zouden dan beiden moeten aanslaan.

Nu moet ik wel zeggen, als de set klant IDs erg groot wordt (zeg 10% van de klant-IDs in je order bestand) dan zal de gemiddelde query optimizer niets anders kunnen als een full-table scan.

Stel, je hebt een tabel "Orders" en een tabel "OrderLines". In plaats van steeds een COUNT() te doen op het aantal OrderLines voor een Order, sla je dat getal op bij een order. Scheelt een subquery oid

Zou niet veel mogen uitmaken. Als orderregels een index op orderid heeft dan is de count() daar alleen een index scan.

[ Voor 18% gewijzigd door LazySod op 02-08-2011 21:25 ]

[Gomez12]

Simpele vraag, kan mysql al meer als 1 index gebruiken op 1 tabel?

En wat zijn je indexen precies?

[voodooless]

Ik weet van PostgreSQL dat het gebruik van IN met heel veel argumenten ertoe kan leiden dat indexen niet gebruikt worden (omdat de query planner denk dat dat niet efficient is). Er zijn dan eigenlijk twee mogelijkheden: parameters tunen, of gebruik van indexen forceren. Ik weet niet of dat bij MySQL ook het gezal is? Wat zegt een explain hierover?

[ Voor 4% gewijzigd door voodooless op 02-08-2011 21:59 ]

[Voutloos]

LazySod schreef op dinsdag 02 augustus 2011 @ 21:23:
Zou niet veel mogen uitmaken. Als orderregels een index op orderid heeft dan is de count() daar alleen een index scan.

Dat kan alsnog significant langzamer zijn. Overigens staat er in de topicstart SUM(aantal) dus dat is al iets lastiger.

Gomez12 schreef op dinsdag 02 augustus 2011 @ 21:42:
Simpele vraag, kan mysql al meer als 1 index gebruiken op 1 tabel?

Kan sinds 5.0, maar gebeurt niet altijd. Zie docs. Ook is LazySod zijn opmerking over percentage matchende rows / cardinality hier van toepassing.

voodooless schreef op dinsdag 02 augustus 2011 @ 21:58:
Ik weet van PostgreSQL dat het gebruik van IN met heel veel argumenten ertoe kan leiden dat indexen niet gebruikt worden (omdat de query planner denk dat dat niet efficient is). Er zijn dan eigenlijk twee mogelijkheden: parameters tunen, of gebruik van indexen forceren. Ik weet niet of dat bij MySQL ook het gezal is?

Geldt ook voor mysql. Er is wel een optie 3: een join ipv IN() gebruiken. Afhankelijk van indexen kan (let op: verre van altijd) het sneller zijn.

Rekcor schreef op dinsdag 02 augustus 2011 @ 21:15:
25 sec

Hmz, dat is meer dan ik zou verwachten, zelfs al zou je hopeloos inefficient door 300k rows lopen. Hoeveel orders en orderregels matchen eigenlijk je where clause?

Maar de belangrijkste vraag blijft welke indexen je nu hebt.

[Gomez12]

Hoe reeel is btw je query?

Want als ik het aantal rows zie uit orderregels (iets in de buurt van 95%) dan vraag ik me af waar je mee bezig bent...

[Rekcor]

Bedankt! Ik heb weer een paar ideeeen opgedaan.

Gomez12 schreef op woensdag 03 augustus 2011 @ 00:34:
Hoe reeel is btw je query?

Want als ik het aantal rows zie uit orderregels (iets in de buurt van 95%) dan vraag ik me af waar je mee bezig bent...

De klant wil weten hoeveel ie dit jaar al heeft omgezet, vorig jaar in dezelfde periode en het hele vorige jaar. Tamelijk reeel

Over de indexes

Maar nu de vraag: hoe kan ik hem optimaliseren? Ik heb al een aantal indices gemaakt voor orderregels: orderId, productId en orderId_2 (=orderId+productId), maar hij blijft als key NULL geven.

[ACM]

Rekcor schreef op woensdag 03 augustus 2011 @ 07:29:
De klant wil weten hoeveel ie dit jaar al heeft omgezet, vorig jaar in dezelfde periode en het hele vorige jaar. Tamelijk reeel

Waarom dan nog de restricties op klant en productids?

Als dat zulke lange lijsten blijven, dan is de kans gewoon groot dat er geen bijzonder efficient plan mogelijk is omdat je een groot deel van de records aanraakt. Hoe groot is het aandeel van de records uit het afgelopen jaar? Als dat een klein deel is, zou je een index op deadline of "klantid, deadline" kunnen proberen.

Als normaliter je niet het gros van de klanten in de IN-clause staat zou je nog kunnen proberen of 'use index(klantid)' achter je orders nut heeft, 't zou kunnen dat dat sowieso al gebeurd met de query van Lazy Sod.

[GlowMouse]

De query in de OP zou zeer snel moeten zijn vanwege de impossible WHERE (orders.deadline<'2010-01-01'
AND orders.deadline>'2011-12-31').

Je schrijft LEFT JOIN, maar door de condities in je WHERE is het een gewone JOIN.

Rekcor schreef op dinsdag 02 augustus 2011 @ 20:52:
Ik heb al een aantal indices gemaakt voor orderregels: orderId, productId en orderId_2 (=orderId+productId), maar hij blijft als key NULL geven.

Eentje daarvan kan sowieso weg, zoek maar eens op mysql, index en prefix. En wat voor rare covering index zit er op orders?

Rekcor schreef op woensdag 03 augustus 2011 @ 07:29:
De klant wil weten hoeveel ie dit jaar al heeft omgezet, vorig jaar in dezelfde periode en het hele vorige jaar. Tamelijk reeel

En daarvoor selecteert hij 1200 klanten en 400 producten?

Er valt niks zinnigs in dit topic te zeggen zonder meer te weten over hoe je data verdeeld is en hoe groot de IN's in je query zijn.

[Rekcor]

ACM schreef op woensdag 03 augustus 2011 @ 08:16:
[...]

Waarom dan nog de restricties op klant en productids?

Hij wil het van bepaalde klanten weten en van bepaalde producten.

voodooless schreef op dinsdag 02 augustus 2011 @ 21:58:
Ik weet van PostgreSQL dat het gebruik van IN met heel veel argumenten ertoe kan leiden dat indexen niet gebruikt worden (omdat de query planner denk dat dat niet efficient is).

Ok. De grap is dat het eerder in een join stond, maar dat ik had bedacht om MySQL een handje te helpen door in een andere query die klantId/productids alvast voor hem op te zoeken

GlowMouse schreef op woensdag 03 augustus 2011 @ 13:23:
Er valt niks zinnigs in dit topic te zeggen zonder meer te weten over hoe je data verdeeld is en hoe groot de IN's in je query zijn.

Wat bedoel je met 'hoe je data verdeeld is'?

[Wolfboy]

Rekcor schreef op donderdag 04 augustus 2011 @ 08:49:

Ok. De grap is dat het eerder in een join stond, maar dat ik had bedacht om MySQL een handje te helpen door in een andere query die klantId/productids alvast voor hem op te zoeken

Ach... goede ingeving.

Ik heb Postgres iig weleens flink het verkeerde queryplan zien kiezen door het gebruik van een join in plaats van een "IN ()"

Maar voor jouw geval zou ik gewoon op gaan slaan hoeveel rijen een order heeft, zoiets is redelijk triviaal om te implementeren (zeker als je triggers gebruikt) en lost direct het probleem op.

[CaVeFiSh]

Ik werk normaal in MSSQL maar ik zou de code zo opbouwen:

code:

SELECT SUM(aantal)
FROM orders 
INNER JOIN orderregels ON (orders.ID=orderregels.orderId)
WHERE orders.klantId BETWEEN 1 AND 1200
AND orders.deadline<'2010-01-01'
AND orders.deadline>'2011-12-31'
AND orderregels.productId BETWEEN 1 AND 400

@GlowMouse, thanks stond idd verkeerd...is nu aangepast

[ Voor 9% gewijzigd door CaVeFiSh op 04-08-2011 19:31 ]

[GlowMouse]

CaVeFiSh schreef op donderdag 04 augustus 2011 @ 16:49:
Ik werk normaal in MSSQL maar ik zou de code zo opbouwen:

Waarom left join schrijven als je join bedoelt?

Rekcor schreef op donderdag 04 augustus 2011 @ 08:49:

[...]

Wat bedoel je met 'hoe je data verdeeld is'?

Welk deel er gematcht wordt door welk deel van de WHERE-clause, eventueel weergegeven in een venn-diagram. En ook of dat per query verschilt.