[MySQL] Optimaliseren van query over 2 tabellen met GROUP BY

[Verwijderd]

Ik probeer in één query een JOIN en een COUNT uit te voeren. Daarbij gaat het om twee (grote)tabellen, die middels een item_id gekoppeld kunnen worden. Er is in beide tabellen een index gelegd op item_id. Wanneer ik alleen de JOIN uitvoer gebeurt dit binnen enkele milliseconden, probleem treedt echter op wanneer ik over het resultaat een GROUP BY uitvoer om de COUNT te kunnen bepalen: dit duurt vaak zo'n minuut. Het lijkt erop alsof MySQL (versie 4.1.12) het resultaat van de JOIN in een tijdelijke tabel wegschrijft, waarna er voor het uitvoeren van de GROUP BY geen indexen meer bekend zijn. Weet iemand hoe ik de query of de tabelstructuur moet optimaliseren?

Dit zijn de tabellen die ik gebruik:

tblCatsPerItem (item_id [index], cat_id) - ongeveer 400.000 records
tblDetailsPerItem (item_id [index], detailtype_id) - ongeveer 4.500.000 records

En dit is de query:

SQL:

SELECT detailtype_id, COUNT(detailtype_id)
FROM tblCatsPerItem, tblDetailsPerItem
WHERE tblCatsPerItem.item_id = tblDetailsPerItem.item_id
AND cat_id = 1
AND detailtype_id IN (1,2,3,4,5,6)
GROUP BY detailtype_id

[justmental]

Hoeveel resultaten komen er uit de query (zonder de group by)?
Sorteren wordt snel duur bij veel records.

[Verwijderd]

Het meest voor de hand liggende lijkt me een index te leggen over item_id, cat_id en detailtype_id.

[Verwijderd]

Er staat een leuk stukje over indexen in de FAQ: P&W FAQ - SQL

Edit:
Gebruik ook eens EXPLAIN om te kijken hoe query uitgevoerd wordt. http://dev.mysql.com/doc/mysql/en/explain.html
Het lijkt me namelijk heel vreemd als de suggesties van BubbelUrp niets uithalen.

[ Voor 54% gewijzigd door Verwijderd op 24-08-2005 13:57 ]

[Verwijderd]

justmental schreef op woensdag 24 augustus 2005 @ 13:40:
Hoeveel resultaten komen er uit de query (zonder de group by)?
Sorteren wordt snel duur bij veel records.

Dit levert meestal heel veel resultaten op, afhankelijk van de opgegeven detailtypes (oftewel de lijst na IN) en de gekozen categorie (cat_id). Worst-case scenario is dat het aantal resultaten gelijk is aan het aantal records in tblDetailsPerItem.

Verwijderd schreef op woensdag 24 augustus 2005 @ 13:40:
Het meest voor de hand liggende lijkt me een index te leggen over item_id, cat_id en detailtype_id.

Sorry, dat had ik erbij moeten vermelden: ik heb ook geprobeerd die indices aan te maken maar dat helpt helaas niet.

[justmental]

Verwijderd schreef op woensdag 24 augustus 2005 @ 13:53:
Dit levert meestal heel veel resultaten op, afhankelijk van de opgegeven detailtypes (oftewel de lijst na IN) en de gekozen categorie (cat_id). Worst-case scenario is dat het aantal resultaten gelijk is aan het aantal records in tblDetailsPerItem.

Probeer eens een group by op de grote tabel zonder die join.
Waarschijnlijk wordt die bijna net zo traag.

Optimalisatie moet je in dat geval zoeken in database configuratie, wellicht kun je de hoeveelheid memory voor sorteeracties configureren.

[Verwijderd]

justmental schreef op woensdag 24 augustus 2005 @ 14:12:
[...]

Probeer eens een group by op de grote tabel zonder die join.
Waarschijnlijk wordt die bijna net zo traag.

Optimalisatie moet je in dat geval zoeken in database configuratie, wellicht kun je de hoeveelheid memory voor sorteeracties configureren.

Deze query heb ik uitgevoerd:

SQL:

SELECT detailtype_id, count(detailtype_id)
FROM tblCBooksDetailsPerItem
WHERE detailtype_id IN (1,2,3,4,5,6,7,8,9,10)
GROUP BY detailtype_id

...en die gaat heel snel: de query duurt dan ongeveer 1 seconde. Dus het trage is volgens mij het joinen voorafgaande aan de group by.

[justmental]

Verwijderd schreef op woensdag 24 augustus 2005 @ 14:37:
Deze query heb ik uitgevoerd:

SQL:

SELECT detailtype_id, count(detailtype_id)
FROM tblCBooksDetailsPerItem
WHERE detailtype_id IN (1,2,3,4,5,6,7,8,9,10)
GROUP BY detailtype_id

...en die gaat heel snel: de query duurt dan ongeveer 1 seconde. Dus het trage is volgens mij het joinen voorafgaande aan de group by.

Dat is wel snel voor 4,5M records
Overigens gebruik je hier een andere tabelnaam, klopt dat?

Als dit het dus niet is dan moet je toch een executieplan hebben, zie de reactie van MrX.

[ACM]

Verwijderd schreef op woensdag 24 augustus 2005 @ 13:53:
Sorry, dat had ik erbij moeten vermelden: ik heb ook geprobeerd die indices aan te maken maar dat helpt helaas niet.

Had je ze ook zo geprobeerd:
[cat_id, item_id]
en
[detailtype_id, item_id]

?

Als ik zo je tabellen bekijk geef ik je de beste kans met op tblCatsPerItem de index [cat_id] en op die andere alleen [item_id] of [item_id, detailstype_id] als er veel details per item zijn. Als er relatief veel categorieen zijn iig.
Als er juist relatief weinig zijn en relatief veel verschillende details, kijk dan ook nog naar [detailtype_id, item_id] en op de andere [cat_id, item_id] of [item_id, cat_id].

Overigens _kan_ MySQL behalve voor de join zelf geen indexen meer gebruiken, want je hebt helemaal geen indexen die dan nog bruikbaar zijn. Alleen de pure join-conditie zelf is geindexeerd in jouw opzet.

Aan de hand van de output van EXPLAIN zul je zeer waarschijnlijk zien dat ie idd ook een tmp table maakt en een file sort doet om de boel te groeperen. Daar doe je weinig aan, maar of dat de meest tijdsverslindende handeling is?
Probeer sowieso nog de door mij genoemde indices eens, zeker als je vrij veel verschillende categorieen hebt.

[ Voor 11% gewijzigd door ACM op 24-08-2005 14:45 ]

[Verwijderd]

justmental schreef op woensdag 24 augustus 2005 @ 14:41:
[...]

Dat is wel snel voor 4,5M records
Overigens gebruik je hier een andere tabelnaam, klopt dat?

Als dit het dus niet is dan moet je toch een executieplan hebben, zie de reactie van MrX.

tblCBooksDetailsPerItem moet inderdaad tblDetailsPerItem zijn. Ik had de naam in de startpost aangepast om 't wat duidelijker te maken (ik heb hier namelijk heel veel identieke tabellen, waaronder CBooks) maar ben vergeten het in mijn laatste reactie ook aan te passen.

Maar goed, dit is het resultaat van EXPLAIN:

code:

id, select_type, table, type, possible_keys, key, key_len, ref, rows, Extra
1, SIMPLE, tblDetailsPerItem, range, PRIMARY,detailtype_id,item_id, detailtype_id, 4, NULL, 285852, Using where
1, SIMPLE, tblCatsPerItem, eq_ref, PRIMARY,cat_id,item_id, PRIMARY, 8, tblDetailsPerItem.item_id,const, 1, Using index

(het type range in de eerste rij schijnt toch een van de slechtste join-types te zijn?)

ACM, ik ga nu even jouw suggestie proberen. Alvast hartelijk dank voor jullie reacties!

[ Voor 4% gewijzigd door Verwijderd op 24-08-2005 16:03 ]

[ACM]

Resultaten van explain zijn overigens duidelijker in een [code]-blok

Hij pakt zo te zien je primary key-indexen, hoe zijn die gemodelleerd? Want als dat al cat_id, item_id is hoef je er natuurlijk geen losse index bij te stoppen