[SQL] Query is sneller MET order by?!?

[bartvb]

Stel ik heb de volgende query:

code:

SELECT 
  t.topic_id 
FROM 
  topics t, 
  users u, 
  posts p, 
  posts p2, 
  users u2 
WHERE 
  t.forum_id = 2 
  AND t.topic_poster = u.user_id 
  AND p.post_id = t.topic_first_post_id 
  AND p2.post_id = t.topic_last_post_id 
  AND u2.user_id = p2.poster_id 
  AND t.topic_type <> 2 
  AND p.post_time >=1023888507 
ORDER BY t.topic_type DESC, t.topic_last_post_id DESC 
LIMIT 0,30

En die duurt 0.05 seconde om uit te voeren. Als ik nu de ORDER BY regel weghaal dan duurt de query ineens 0.35 seconde, 700% langzamer dus...

Hoe kan dit? Mij lijkt dat je DB minder hoeft te doen als ie niet hoeft te sorteren, hij mag dan lekker doen wat ie zelf wil en ik ga er van uit dat hij dan doet wat het snelste is? Lekker vaag dit

Iemand enig idee?

[Feyd-Rautha]

dat zal te maken hebben met de correctie indexen die er liggen.

Bij het sorteren, zal het DBMS gewoon die index overlopen en netjes tonen.

Wanneer er niet moet gesorteerd worden moet die DBMS de volledige tabel overlopen en door die WHERE-voorwaarden ook nog eens joinen enzo

... dat denk ik toch tenminste ...

[Dennis]

Ik weet niet waarom, maar vind het eigenlijk wel logisch, omdat de db minder na hoeft te denken als je meer info doorgeeft .

Welke DB had je gebruikt trouwens

[JayTaph]

Pin me er niet op vast, maar volgens mij komt dat door het feit dat de optimizer in een normale query gewoon alle entries teruggeeft, en daarna pas gaat kijken naar waar het allemaal aan moet voldoen (dus type mag geen 2 zijn, topic_poster moet gelijk zijn aan user_id etc).

Op het moment dat je een order_by meegeeft, moet er gesorteerd worden, en de optimizer ziet al dat alles wat geen topic_type 2 heeft, en geen topic_last_post_id dat gelijk is aan post_id, dat deze al niet eens verder te worden hoeven bestudeerd..

Naja.. beetje vage uitleg, maar volgende mij komt het dus doordat de optimizer al vantevoren een hoop kan weggooien.

[Feyd-Rautha]

Op woensdag 26 juni 2002 16:01 schreef JayTaph het volgende:
Pin me er niet op vast, maar volgens mij komt dat door het feit dat de optimizer in een normale query gewoon alle entries teruggeeft, en daarna pas gaat kijken naar waar het allemaal aan moet voldoen (dus type mag geen 2 zijn, topic_poster moet gelijk zijn aan user_id etc).

volgens mij is dit niet juist hoor.
Wat ik wel zeker weet is dat de Oracle Query-optimizer een wezenlijk verschil maakt tussen bv:
- WHERE tblDummy.naam like %a% AND tblDummy.leeftijd > 18
of
- WHERE tblDummy.leeftijd > 18 AND tblDummy.naam like %a%

bij het eerste geval wordt eerst die naam gecontroleerd wat heel lang kan duren (ieder lettertje overlopen en controleren) en pas dan, op het resultaat van die eerste voorwaarde, worden de records geselecteerd die voldaan aan de tweede voorwaarde.

Het lijkt mij dus duidelijk dat de eerste WHERE heel wat langer zal duren dan de tweede aangezien bij de tweede WHERE eerst al een beperkte, vlugge, selectie wordt ondernomen (controleren op >18 gaat aanzienlijker vlugger dan like %a%)

[whoami]

Op woensdag 26 juni 2002 16:00 schreef ddc het volgende:
Ik weet niet waarom, maar vind het eigenlijk wel logisch, omdat de db minder na hoeft te denken als je meer info doorgeeft .

Nee hoor, als je de order by weglaat dan haalt hij gewoon alle rijen op die voldoen daan de query. Zet je er een order by bij, dan gaat het DBMS nadat hij alle rijen heeft opgehaald, ze nog eens moeten sorteren.

Zeer vreemd dus dat dat trager gaat.

Wat Avalanche zegt, klopt volgens mij ook niet. Want die 2 queries worden op dezelfde tabellen uitgevoerd, en dus wordt er gebruik gemaakt van dezelfde indexen.

[whoami]

Op woensdag 26 juni 2002 16:06 schreef -Avalanche- het volgende:

bij het eerste geval wordt eerst die naam gecontroleerd wat heel lang kan duren (ieder lettertje overlopen en controleren) en pas dan, op het resultaat van die eerste voorwaarde, worden de records geselecteerd die voldaan aan de tweede voorwaarde.

Het lijkt mij dus duidelijk dat de eerste WHERE heel wat langer zal duren dan de tweede aangezien bij de tweede WHERE eerst al een beperkte, vlugge, selectie wordt ondernomen (controleren op >18 gaat aanzienlijker vlugger dan like %a%)

Een like %a% is zowieso al traag, want die conditie kan geen gebruik maken van indexen.

[whoami]

Op woensdag 26 juni 2002 16:01 schreef JayTaph het volgende:
Pin me er niet op vast, maar volgens mij komt dat door het feit dat de optimizer in een normale query gewoon alle entries teruggeeft, en daarna pas gaat kijken naar waar het allemaal aan moet voldoen (dus type mag geen 2 zijn, topic_poster moet gelijk zijn aan user_id etc).

Jij bedoelt dus dat het DBMS gewoon alle rijen uit de table gaat gaan ophalen en daarna pas gaat gaan kijken welke rows er niet moeten teruggegeven worden?
Dit is dus een table-scan, maar als er indexen liggen wordt er geen table-scan uitgevoerd lijkt me.

Op het moment dat je een order_by meegeeft, moet er gesorteerd worden, en de optimizer ziet al dat alles wat geen topic_type 2 heeft, en geen topic_last_post_id dat gelijk is aan post_id, dat deze al niet eens verder te worden hoeven bestudeerd..

Volgens mij gebeurt de ORDER BY pas nadat alle rows die aan de query voldoen opgehaald zijn. Pas als alle rows opgehaald zijn kunnen ze nl. maar gesorteerd worden.

[mbravenboer]

Die query optimizer zuigt waarschijnlijk gewoon . Een goede query optimizer moet rekening heden met 'interesting orders' bij het opstellen van een query-plan. Als gunstig is om een tussen-resultaat in een bepaalde volgorde op te leveren, moet dit meegenomen worden als optie voor het query-plan. Als een query compiler dit niet doet, is hij gewoon dom bezig.

Waarschijnlijk is het zo dat de order operatie niet op het allerlaast wordt uitgevoerd, maar 'omlaag' gepushed is tot een bepaalde locatie in de boom. Dit kan er voor zorgen dat operaties hoger in de boom gebruik kunnen maken van deze ordering en dus efficienter uitgevoerd kunnen worden.

Dat de query compiler deze 'interessante ordening' niet kan meenemen in een het opstellen van een query plan bij een query zonder order-by is jammer, maar het kan bijvoorbeeld ook zo zijn dat het statistisch gezien niet duidelijk is of die ordering echt interessant is: het kan net goed uitpakken in dit geval, wat echter niet uit de statistieken die het DBMS bijhoudt kan worden afgeleid.

Welk DBMS gebruik je?

[bartvb]

Gaat hier over MySQL.
Verklaring van mbravenboer klinkt nog het meest waarschijnlijk Eigenlijk ergens ook wel logisch (delen er van, rest gaat me wat te diep maar daardoor niet minder interessant ).

Hieronder nog een explain van de query.
users heeft 4000 entries
posts heeft 600.000 entries
topics heeft 60.000 entries
forum_id = 2 heeft 7353 topics.

code:

*************************** 1. row ***************************
      table: t
       type: ref
possible_keys: forum_id,topic_last_post_id,topic_first_post_id,topic_first_post_id_2
        key: forum_id
    key_len: 2
        ref: const
       rows: 6581
      Extra: where used; Using filesort
*************************** 2. row ***************************
      table: u
       type: eq_ref
possible_keys: PRIMARY
        key: PRIMARY
    key_len: 3
        ref: t.topic_poster
       rows: 1
      Extra: Using index
*************************** 3. row ***************************
      table: p
       type: eq_ref
possible_keys: PRIMARY,post_time
        key: PRIMARY
    key_len: 3
        ref: t.topic_first_post_id
       rows: 1
      Extra: where used
*************************** 4. row ***************************
      table: p2
       type: eq_ref
possible_keys: PRIMARY,poster_id
        key: PRIMARY
    key_len: 3
        ref: t.topic_last_post_id
       rows: 1
      Extra: 
*************************** 5. row ***************************
      table: u2
       type: eq_ref
possible_keys: PRIMARY
        key: PRIMARY
    key_len: 3
        ref: p2.poster_id
       rows: 1
      Extra: Using index