Complexe (My)SQL-query: where vs. on vs. subquery

[SvMp]

Op mijn werk liep ik tegen een probleem met traagheid aan. Een complexe query verzamelt data uit een aantal tabellen met joins en left joins. Nu er 400 artikelen in zijn gevoerd.

De query wordt traag, zodra deze wordt uitgevoerd in het kader van een zoek-functie. De zoek-functie zoekt in bijvoorbeeld de naam.

Trage situatie: Aan het eind van de query staat een where-clause met de zoek-string, zoals "WHERE (naam LIKE ?) AND (producent_naam LIKE ?)".

Die traagheid heb ik simpelweg op kunnen lossen, door de where-clause helemaal achter wege de laten en de zoek-criteria bij de joins van de betreffende tabel te plaatsen achter ON, dus bijvoorbeeld ON ... AND (naam LIKE ?). En opeens gaat de query meer dan 100 keer zo snel!

Het aantal artikelen kan de oorzaak niet zijn, 400 is niets.

Met Googlen kon ik niet echt een sluitende verklaring vinden. Wel andere ervaringen met betere prestaties dmv. verplaatsing van WHERE-claude naar ON ...

Ik vond nog wel een derde alternatief: Een subquery icm. JOIN

code:

SELECT H.INVOICE, H.COMPANY, H.CUSTNBR, H.INVDATE,
       D.LINE, D.ITEM, D.QTY                      
  FROM INVHDR AS H                                
  LEFT JOIN (SELECT *                             
               FROM INVDTL                        
              WHERE ITEM = 'AB1441') AS D         
    ON H.INVOICE = D.INVOICE

Bron: http://www.itjungle.com/fhg/fhg111407-story01.html


Wat is het beste?
Toen ik de query schreef, twijfelde ik al of ik het nou bij de ON of in de WHERE-clause zou plaatsen. Gekozen voor de WHERE-claude en de ON alleen gebruikt voor koppeling tussen tabellen. Nu ik een gevulde database heb, heb ik kunnen ontdekken dat dit niet de beste manier is.

Is er een bepaalde regel? Geldt altijd dat je zoveel mogelijk bij ON moet onderbrengen bij joins?

[fleppuhstein]

En wat zegt een EXPLAIN over de verschillende versies van queries.

En zonder where hoeveel rows worden er gereturned, maw in hoeveel rows wordt er een string match op meerdere kolommen gedaan ?

[NMe]

Het verschil tussen een WHERE en een ON-clause is dat die ON-filtering eerder gebeurt en dus beter te optimaliseren valt door je DBMS. Dat betekent niet dat je zomaar alles in de ON kwijt moet, dat betekent dat je na moet denken wat daar wel en niet thuishoort.

Overigens is LIKE per definitie traag en afhankelijk van je situatie is full text search mogelijk een stuk beter.

[Boss]

Alle indexen zijn goed aangelegd op de diverse tabellen?

[jbdeiman]

In principe kan je het zo zien:

Als je bij JOINS de voorwaarde in de ON plaats, past SQL op dat punt ook die voorwaarde al toe, hij Joint alleen de records die aan die voorwaarde voldoen.
Plaats je de voorwaarde in de WHERE, dan gaat er eerst een JOIN plaatsvinden tussen alle tabellen, en wordt dan gekeken of die voorwaarde (nog) voldoet.

MySQL filtert dus eerder op de conditie en heeft daardoor minder rijen die doorlopen worden.

[Remus]

jbdeiman schreef op zaterdag 02 juli 2011 @ 09:16:
In principe kan je het zo zien:

Als je bij JOINS de voorwaarde in de ON plaats, past SQL op dat punt ook die voorwaarde al toe, hij Joint alleen de records die aan die voorwaarde voldoen.
Plaats je de voorwaarde in de WHERE, dan gaat er eerst een JOIN plaatsvinden tussen alle tabellen, en wordt dan gekeken of die voorwaarde (nog) voldoet.

MySQL filtert dus eerder op de conditie en heeft daardoor minder rijen die doorlopen worden.

Conceptueel heb je gelijk, maar het staat een database natuurlijk vrij om allerlei transformaties en optimalisaties uit te voeren zolang het eind resultaat maar hetzelfde is, denk bijvoorbeeld aan het niet gebruiken van rows uit de linker helft van een join als ze toch al niet aan de WHERE voldoen, etc.
Overigens is er natuurlijk een groot verschil tussen een conditie in een WHERE en bijvoorbeeld een conditie in ON van een LEFT JOIN. Wordt in het eerste geval niet aan de conditie gedaan dan heb je geen row, in het andere geval heb je wel een row, maar is de rechterkant volledig NULL.

[cariolive23]

NMe schreef op vrijdag 01 juli 2011 @ 23:24:
Het verschil tussen een WHERE en een ON-clause is dat die ON-filtering eerder gebeurt en dus beter te optimaliseren valt door je DBMS. Dat betekent niet dat je zomaar alles in de ON kwijt moet, dat betekent dat je na moet denken wat daar wel en niet thuishoort.

Dit verschilt per database, is zeker geen generiek antwoord. Je moet dit voor jouw merk database uitzoeken en dus ook het queryplan uitpluizen (met EXPLAIN).

Overigens is LIKE per definitie traag en afhankelijk van je situatie is full text search mogelijk een stuk beter.

Dit ligt vooral aan de plaats van de wildcard en het soort index wat jouw merk database kan gebruiken.

LIKE 'j%' kan gewoon een btree index, die is gesorteerd en de j begint altijd ná de i en vóór de k. Een index kan dus enorm schelen met het zoeken. Echter, LIKE '%j' werkt niet, de database moet álles doorzoeken om matches te vinden. Er zijn ook nog speciale indexen die hier helemaal geen problemen mee hebben en waar vrijwel alles mét index is op te zoeken.

De configuratie van de database server kan ook nog een grote invloed hebben op de queryplannen dus de performance van jouw queries.

Maar begin eens met EXPLAIN.

[NMe]

Klopt, cariolive23. Maar de topicstarter heeft het over MySQL dus daar was mijn antwoord ook op gericht. EXPLAIN is hoe dan ook een goed idee.

[EfBe]

offtopic:
Wellicht off-topic, maar dat moet dan maar: dit forum gaat over Software engineering en architecture, niet over programmeer vragen. Waarom is dit topic (en andere in dit forum) niet gemoved naar 'programming' ?

[RobIII]

EfBe schreef op dinsdag 05 juli 2011 @ 08:41:

offtopic:
Wellicht off-topic, maar dat moet dan maar: dit forum gaat over Software engineering en architecture, niet over programmeer vragen. Waarom is dit topic (en andere in dit forum) niet gemoved naar 'programming' ?

Omdat we ook niet alles zien
Het staat je altijd vrij een Topic Report () te doen.

SEA >> PRG