[MySQL] Using temporary bij GROUP + ORDER

[Anoniem: 23996]

Ik heb last van een zeer bijzonder performance probleem met een InnoDB UTF-8 MySQL v4.1.41 database:

Tabel `contacts`
( id, name, ... )
PRIMARY = `id`
INDEX = `name`

Tabel `contacts_has_contacts`
(id, parent_id, child_id )
PRIMARY = `id`
UNIQUE = ( parent_id, child_id )

De columns `parent_id` en `child_id` refereren beide naar `contacts`.`id`. De tabel zorgt er dus voor dat contactpersonen onder elkaar gehangen kunnen worden. In de praktijk is de query dus wat complexer, maar op dit niveau treedt het performance probleem al op.

De query

SQL:

SELECT c.id
FROM contacts c
LEFT JOIN contacts_has_contacts h ON c.id = h.parent_id
GROUP BY c.id
ORDER BY c.name ASC
LIMIT 0 , 20

EXPLAIN in MySQL v5.1.53 en v5.1.41

code:

id  select_type   table   type   possible_keys   key            key_len   ref   rows   Extra
1   SIMPLE        c       index  NULL            name           194       NULL  20     Using index; Using temporary
1   SIMPLE        cch     ref    parent_child    parent_child   2         c.id  1      Using index

In een iets oudere MySQL versie wordt het zelfs nog wat erger: (zie 'rows')

EXPLAIN in MySQL v5.0.37

code:

id  select_type   table   type   possible_keys   key            key_len   ref   rows   Extra
1   SIMPLE        c       index  NULL            name           194       NULL  9974   Using index; Using temporary
1   SIMPLE        cch     ref    parent_child    parent_child   2         c.id  1      Using index

Why oh why gebruikt ie een temporary table???? Bij nog iets meer joins gaat ie ook nog eens filesorten, dus dan is ie nog trager. Ik ben radeloos!

[Voor 15% gewijzigd door Anoniem: 23996 op 24-06-2011 13:54]

[FragFrog]

Zit er uberhaupt wel een index op contacts.name? Ik zou eigenlijk verwachten dat deze wel gebruikt wordt; having said that, group by en orders zijn in MySQL wel vaker een beetje vreemd

[Voor 27% gewijzigd door FragFrog op 24-06-2011 14:18]

[Anoniem: 23996]

FragFrog schreef op vrijdag 24 juni 2011 @ 14:17:
Zit er uberhaupt wel een index op contacts.name? Ik zou eigenlijk verwachten dat deze wel gebruikt wordt; having said that, group by en orders zijn in MySQL wel vaker een beetje vreemd

De EXPLAIN toont aan dat de index op `name` wel gebruikt wordt.

Wat betreft het vreemde gedrag in MySQL; dat klopt. De vraag is alleen; hoe ga ik daarmee om?

[FragFrog]

Mea culpa, overheen gelezen. Bij nader inzien vraag ik me af of het uberhaupt wel mogelijk is dit te doen zonder een temp table na sorteren (of subquery, maar daar zal het niet sneller van worden). Benieuwd of er een antwoord komt hierop.

[LuCarD]

Using temporary

To resolve the query, MySQL needs to create a temporary table to hold the result. This typically happens if the query contains GROUP BY and ORDER BY clauses that list columns differently.

http://dev.mysql.com/doc/refman/5.0/en/explain-output.html

[Anoniem: 23996]

In some cases, MySQL cannot use indexes to resolve the ORDER BY, although it still uses indexes to find the rows that match the WHERE clause. These cases include the following:

* You have different ORDER BY and GROUP BY expressions.

Kortom; een workaround is noodzakelijk.

Hoe kan ik een unique forceren op `id` zonder GROUP BY of DISTINCT te gebruiken? (kortom, maximaal 1 row per JOIN)

[Voor 83% gewijzigd door Anoniem: 23996 op 24-06-2011 15:12]

[Voutloos]

Je hebt je query nu dusdanig versimpeld dat de hele join niet nodig is.

Maar goed, met een derived table (subquery in de from clause) is het zaakje wel snel te krijgen hoor. Iets als
SELECT dingen
FROM (SELECT id FROM Contacts ORDER BY name LIMIT 20) LEFT JOIN Rapapa rapapa
GROUP BY zaken
ORDER BY stuff



Je krijgt wellicht alsnog een temporary explain hint, maar je weet dan dat altijd over 20 items gaat, dus lekker boeiend.

[Voor 16% gewijzigd door Voutloos op 24-06-2011 15:37]

[Anoniem: 23996]

Voutloos schreef op vrijdag 24 juni 2011 @ 15:36:
Je hebt je query nu dusdanig versimpeld dat de hele join niet nodig is.

Maar goed, met een derived table (subquery in de from clause) is het zaakje wel snel te krijgen hoor. Iets als
SELECT dingen
FROM (SELECT id FROM Contacts ORDER BY name LIMIT 20) LEFT JOIN Rapapa rapapa
GROUP BY zaken
ORDER BY stuff



Je krijgt wellicht alsnog een temporary explain hint, maar je weet dan dat altijd over 20 items gaat, dus lekker boeiend.

OK! De pagination functie queried echter eerst nog zonder LIMIT clause. Hoe valt 't in dat geval op te lossen?

[Voutloos]

Dan hoef je enkel COUNT(*) te doen en kan je ORDER BY en andere shit gewoon weglaten.
[/piece-of-cake]

[Voor 9% gewijzigd door Voutloos op 24-06-2011 16:01]

[Anoniem: 23996]

Het werkt briljant snel!

Echter 1 belangrijk probleem ontstaat nu: vanwege de GROUP BY op de parent tabel, bestaat de mogelijkheid dat de uiteindelijke resultset minder rows oplevert dan beoogt was met de LIMIT in de derived table. In dit geval gebeurt dat wanneer er meerdere child-contacts per parent-contact zijn.

Een random testje met onderstaande query leverde me 13 results op:

SQL:

SELECT dc.id
FROM (
    SELECT c.id
    FROM contacts c
    LEFT JOIN contacts_has_contacts cch ON c.id = h.parent_id
    ORDER BY c.name ASC
    LIMIT 30
) dc
GROUP BY dc.id

Is daar toevallig ook nog een oplossing voor te verzinnen? Het aantal childs moet "onbeperkt" zijn.

[Voor 3% gewijzigd door Anoniem: 23996 op 29-06-2011 11:52]

[Anoniem: 23996]

*schop*

De combinatie van een verschillende GROUP BY en ORDER BY clause, vereist in MySQL een temporary table. Zoals hierboven te zien, valt dat te ontwijken d.m.v. een derived table. De LIMIT wordt in dat geval echter onbetrouwbaar. Iemand die daar een oplossing voor weet?

Hieronder een ranzige en onbetrouwbare oplossing voor een gewenste resultset van 30 rows.

SQL:

SELECT t3.id
FROM (
    SELECT t1.id
    FROM table1 t1
    LEFT JOIN table1 t1 ON t1.id = t2.parent_id
    ORDER BY t1.col1 ASC
    LIMIT 100
) t3
GROUP BY t3.id
LIMIT 30

[drm]

Waar is die group by eigenlijk goed voor Of selecteer je in de daadwerkelijke query nog andere velden of aggregates?

[Anoniem: 23996]

drm schreef op maandag 04 juli 2011 @ 20:44:
Waar is die group by eigenlijk goed voor Of selecteer je in de daadwerkelijke query nog andere velden of aggregates?

Mijn doel is om gegarandeerd maar 1 record per `table1`.`id` te krijgen. Vanwege de een-op-meer relatie met de LEFT JOIN is hiervoor een GROUP BY noodzakelijk. (of DISTINCT, maar dat is in feite hetzelfde)

[sky-]

Group By is helemaal niet hetzelfde als distinct. Group By alleen gebruiken met aggregate functions (SUM etc), niet anders.

Stel je MySQL eens goed in, als je dat doet mekkert ie dat je niet zomaar een GROUP BY kan gebruiken.

Wat dat betreft is MySQL echt bagger

[Anoniem: 23996]

sky- schreef op dinsdag 05 juli 2011 @ 16:52:
Group By is helemaal niet hetzelfde als distinct. Group By alleen gebruiken met aggregate functions (SUM etc), niet anders.

Voor zover het doel van de queries in dit topic betreft, zijn GROUP BY en DISTINCT wel degelijk vergelijkbaar. Zie http://dev.mysql.com/doc/...istinct-optimization.html.

[Voor 36% gewijzigd door Anoniem: 23996 op 06-07-2011 14:03]

[drm]

Ben toch nog een beetje benieuwd naar de situatie, want waarom heb je die ID's gesorteerd op naam nodig? Pure nieuwsgierigheid, hoor

[GlowMouse]

Wat je zei op vrijdag 24 juni 2011 14:51 klopt. Dit is de workaround:

SQL:

SELECT c.id
FROM contacts c
LEFT JOIN contacts_has_contacts h ON c.id = h.parent_id
GROUP BY c.name,c.id
ORDER BY c.name ASC
LIMIT 0 , 20

en de index op (name) ombouwn naar (name,id).

Waarom heb je in contacts_has_contacts een overbodige id-kolom?

[Voor 3% gewijzigd door GlowMouse op 08-07-2011 11:54]

[RobIII]

-hier stond onzin-

[Voor 93% gewijzigd door RobIII op 08-07-2011 11:58]

[Anoniem: 23996]

GlowMouse schreef op vrijdag 08 juli 2011 @ 11:54:
Wat je zei op vrijdag 24 juni 2011 14:51 klopt. Dit is de workaround:

SQL:

SELECT c.id
FROM contacts c
LEFT JOIN contacts_has_contacts h ON c.id = h.parent_id
GROUP BY c.name,c.id
ORDER BY c.name ASC
LIMIT 0 , 20

en de index op (name) ombouwen naar (name,id).

YEAH!! Het is exact wat ik zocht! Gezien de wijze waarop MySQL omgaat met INDEXes ook eigenlijk wel logisch, maar je moet er maar opkomen Thanks thanks thanks! Ik had de hoop al opgegeven!

Er ontstaat echter wel een kleine volgende vraag. Ten gunste van de pagination wordt de query eerst zonder LIMIT uitgevoerd. In de werkelijke situatie bevat m'n SELECT nogal wat columns. Laten we het voor het gemak van deze discussie maar hebben over een wildcard (*). Deze columns staan niet in een index, waardoor er toch ineens weer een filesort nodig is. Is er een workaround om toch een hele lijst aan columns te SELECTen, zonder dat die in de INDEX hoeven te staan?

[Voutloos]

Die vraag heb je al eerder gesteld en het antwoord heb ik al eerder gegeven.

[GlowMouse]

Anoniem: 23996 schreef op zondag 10 juli 2011 @ 13:37:
[...]

YEAH!! Het is exact wat ik zocht! Gezien de wijze waarop MySQL omgaat met INDEXes ook eigenlijk wel logisch, maar je moet er maar opkomen Thanks thanks thanks! Ik had de hoop al opgegeven!

Er ontstaat echter wel een kleine volgende vraag. Ten gunste van de pagination wordt de query eerst zonder LIMIT uitgevoerd. In de werkelijke situatie bevat m'n SELECT nogal wat columns. Laten we het voor het gemak van deze discussie maar hebben over een wildcard (*). Deze columns staan niet in een index, waardoor er toch ineens weer een filesort nodig is. Is er een workaround om toch een hele lijst aan columns te SELECTen, zonder dat die in de INDEX hoeven te staan?

Ik snap je vraag niet, het aantal kolommen na SELECT heeft niks met het gebruik van een index te maken. Hooguit voor een covering index, maar dat heeft geen invloed op filesort.

[Anoniem: 23996]

Voutloos schreef op zondag 10 juli 2011 @ 15:37:
Die vraag heb je al eerder gesteld en het antwoord heb ik al eerder gegeven.

Een Derived Table is in dit geval geen oplossing, omdat het zonder de LIMIT alle records JOINt.

Het weglaten van de ORDER BY doe ik al, maar dat heeft helaas niets te maken met de index behoefte die er nu optreedt. Ik zou in de pagination query de SELECT columns weg kunnen laten, maar dat zou ik liever vermijden gezien de werking van m'n pagination class.

[Voor 14% gewijzigd door Anoniem: 23996 op 11-07-2011 10:25]

[Voutloos]

Ik zie het probleem ook niet zo. Als je het totaal aantal records nodig hebt doe je gewoon een zo simpel mogelijke COUNT(*) of een CALC_FOUND_ROWS en klaar ben je.

(En het liefst test je beide queries, want CALC_FOUND_ROWS is misschien wel eenvoudiger is, maar soms ook onwerkbaar langzaam omdat het niet optimaal wordt uitgevoerd.

[Anoniem: 23996]

GlowMouse schreef op zondag 10 juli 2011 @ 16:16:
[...]

Ik snap je vraag niet, het aantal kolommen na SELECT heeft niks met het gebruik van een index te maken. Hooguit voor een covering index, maar dat heeft geen invloed op filesort.

Als ik dit doe:

SQL:

SELECT c.id
FROM contacts c
LEFT JOIN contacts_has_contacts h ON c.id = h.parent_id
GROUP BY c.name,c.id
ORDER BY c.name ASC

Gaat alles perfect. Hij gebruikt netjes de index die staat ingesteld op de columns name+id. De query is klaar in 0,0007 seconden over een tabel met 10.000 records. Wanneer ik echter een extra column toevoeg:

SQL:

SELECT c.id, c.col_x
FROM contacts c
LEFT JOIN contacts_has_contacts h ON c.id = h.parent_id
GROUP BY c.name,c.id
ORDER BY c.name ASC

Weigert MySQL opeens om op de primaire tabel een index te gebruiken, duurt de query 0,4 seconden en de EXPLAIN toont "Using temporary, using filesort". Sterker nog; zelfs de volgende query toont zelfs nog "Using filesort", ondanks de index op name+id

SQL:

SELECT c.id, c.col_x
FROM contacts c
GROUP BY c.name,c.id

Ik begrijp dat die laatste query in de praktijk onzin is, maar het gaat even om de raadselachtige filesort.

[Voor 26% gewijzigd door Anoniem: 23996 op 11-07-2011 11:19]

[drm]

Kun je geen index hint gebruiken?

[Anoniem: 23996]

drm schreef op maandag 11 juli 2011 @ 14:51:
Kun je geen index hint gebruiken?

Nee, helaas pakt ie de index ook dan niet op.

Het probleem is volgens mij dat MySQL eerst de hele query probeert te resolven; en als dat gelukt is vraagt ie alsnog de bijbehorende columns op. Heb hier ooit eens ergens over gelezen, maar kan het niet meer terugvinden. Ik geloof dat het met covering indexes te maken heeft. De truck was iig om de query te LMITten, maarjah dat is nou net wat ik dus niet wil in dit geval...

[GlowMouse]

Gebruik je USE INDEX of FORCE INDEX?

Met die extra kolom, gebruikt hij dan een index waarin id, name en col_x zitten? Zoja, kan IGNORE INDEX je helpen.

[0siris]

<goedbedoeld advies, zeker geen "echte" oplossing!>
kun je, als dat temporary spul echt niet te voorkomen is, temporary in memory aanleggen?
En, werkt 't misschien wel in MySQL 5.5? (Mocht je Debian gebruiken, kijk eens naar bijv. Percona
</goedbedoeld advies, zeker geen "echte" oplossing!>

[GlowMouse]

0siris schreef op maandag 11 juli 2011 @ 20:15:
<goedbedoeld advies, zeker geen "echte" oplossing!>
kun je, als dat temporary spul echt niet te voorkomen is, temporary in memory aanleggen?
En, werkt 't misschien wel in MySQL 5.5? (Mocht je Debian gebruiken, kijk eens naar bijv. Percona
</goedbedoeld advies, zeker geen "echte" oplossing!>

Afhankelijk van het kolomtype van naam en het aantal records gebeurt dat al.
En Percona Server heeft geen andere query optimizer, die gebruik je hoofdzakelijk voor de betere monitoringmogelijkheden.

[Voor 8% gewijzigd door GlowMouse op 11-07-2011 20:49]

[Anoniem: 23996]

GlowMouse schreef op maandag 11 juli 2011 @ 20:04:
Gebruik je USE INDEX of FORCE INDEX?

Met die extra kolom, gebruikt hij dan een index waarin id, name en col_x zitten? Zoja, kan IGNORE INDEX je helpen.

Ik heb beide getest. Nu voor de verandering maar eens op een andere versie. En daar gebeurt wat geks:

In MySQL 5.0.37 wordt geen enkele index opgepakt, ongeacht het gebruik van USE/FORCE INDEX. In MySQL 5.1.53 echter werkt FORCE INDEX wel. Helaas echter zit ik nog een maand aan 5.0.37 vast.

Vanwaar dit vreemde gedrag? Waarom gebruikt MySQL uit zichzelf geen index?

[Voor 20% gewijzigd door Anoniem: 23996 op 11-07-2011 20:57]

[Gomez12]

Anoniem: 23996 schreef op maandag 11 juli 2011 @ 20:56:
[...]

Vanwaar dit vreemde gedrag? Waarom gebruikt MySQL uit zichzelf geen index?

Probeer eens een analyze over je tabellen heen te gooien...

Misschien dat de internal statistics een beetje rot zijn en hij daarom besluit dat filesort sneller zou moeten zijn dan index?

[Anoniem: 23996]

Gomez12 schreef op maandag 11 juli 2011 @ 21:00:
[...]

Probeer eens een analyze over je tabellen heen te gooien...

Misschien dat de internal statistics een beetje rot zijn en hij daarom besluit dat filesort sneller zou moeten zijn dan index?

Alle tabellen geven status OK. Dit bood dus helaas ook geen soelaas.

[Voor 5% gewijzigd door Anoniem: 23996 op 13-07-2011 11:37]