[MySQL] Slow query.. te optimizen?

[RvdH]

Mijn slow-query-log staat vol met 1 bepaalde query, en nu ben ik niet zo'n SQL guru maar ik vraag me af of deze te optimizen is. Dit is de query:

SELECT B.RecordNumber FROM books AS B, yauthor as YA1 USE INDEX (WordBook), ytitle AS YT1 USE INDEX (WordBook), ytitle AS YT2 USE INDEX (WordBook) WHERE YA1.BookNumber = B.RecordNumber AND YA1.WordNumber = 249 AND YT1.BookNumber = B.RecordNumber AND YT2.BookNumber = B.RecordNumber AND YT1.WordNumber = 4985 AND YT2.WordNumber = 29534 AND (B.Cover = 1) LIMIT 0,51;

books is een tabel met zo'n 2 miljoen boeken erin, met omschrijvingen e.d..
ytitle is de titel van het boek.
De query zoekt dus alle (book) records op van een bepaalde auteur, waar sleutel woorden in voorkomen die ingevoerd zijn door de user, in dit geval woorden 249, 29534 en 4985.

Dit soort queries duren gemiddeld zo'n 15 seconden, wat natuurlijk veels te lang is. Enig idee hoe dat sneller kan?
[edit: oops, yauthor is geen tabel met auteurs info, maar een tabel met WordNumbers en BookNumbers]

[Grum]

indices aanmaken?

[RvdH]

Op maandag 01 april 2002 17:57 schreef Grum het volgende:
indices aanmaken?

Er staat niet voor niks "USE INDEX" in die query..

[ACM]

Op maandag 01 april 2002 19:01 schreef strlen het volgende:
Er staat niet voor niks "USE INDEX" in die query..

Heb je ook dmv EXPLAIN gekeken of ie ook de goede indices gebruikt?

Of laat die USE INDEX es weg en kijk wat de optimiser dan probeert te doen, want bij een goede index zal de optimiser die zelf al kiezen.

Verder kan grum's tip nog steeds goed zijn, angezien je nog geen (samengestelde? ) index lijkt te hebben op de authorid van je books-tabel?

[RvdH]

Op maandag 01 april 2002 19:46 schreef ACM het volgende:

[..]

Heb je ook dmv EXPLAIN gekeken of ie ook de goede indices gebruikt?

Of laat die USE INDEX es weg en kijk wat de optimiser dan probeert te doen, want bij een goede index zal de optimiser die zelf al kiezen.

Daarom staat dat er tussen.. voorheen werd er een (erg oude) MySQL gebruikt die die syntax niet ondersteunde, en explain liet zien dat ie af en toe wel, en af en toe niet de index gebruikte. Nu gebruikt hij 'm dus wel.

Verder kan grum's tip nog steeds goed zijn, angezien je nog geen (samengestelde? ) index lijkt te hebben op de authorid van je books-tabel?

Books tabel heeft geen authorid.

[dusty]

Even de query in een meer leesbare en begrijpelijke vorm:

code:

SELECT 
  B.RecordNumber 
FROM 
  books AS B, 
  yauthor as YA1 USE INDEX (WordBook), 
  ytitle AS YT1 USE INDEX (WordBook), 
  ytitle AS YT2 USE INDEX (WordBook) 
WHERE 
  YA1.BookNumber = B.RecordNumber 
AND   
  YA1.WordNumber = 249 
AND 
  YT1.BookNumber = B.RecordNumber 
AND 
  YT2.BookNumber = B.RecordNumber 
AND 
  YT1.WordNumber = 4985 
AND 
  YT2.WordNumber = 29534 
AND 
 (B.Cover = 1) 
LIMIT 0,51;

[RvdH]

Op maandag 01 april 2002 20:30 schreef dusty het volgende:
Even de query in een meer leesbare en begrijpelijke vorm:

Je bent een engel

[Grum]

wat wil je in godsnaam doen met je query ?

streepje lees foutje

[dusty]

Handmatig optimizen is vaak nog steeds de beste manier. Het begrijpen hoe een database omspringt met een query is ook erg makkelijk.

bijvoorbeeld hierbij:

YA1.BookNumber = B.RecordNumber
AND
YA1.WordNumber = 249

Hij koppelt de YA1 tabel compleet aan de B tabel. Waar de booknumber gelijk is aan de recordnumber.

Daarna gaat hij van de gecreerde "tabel" filteren waarbij ua1.wordnumber gelijk is aan 249.

Als YA1 1.000 records bevat. en tabel B 100 records waaraan B.recordnumber gelijk is aan booknumber krijg je dus 100*1.000= 100.000 records in je geheugen te staan.

Als in YA1.wordnumber nou maar 10 keer 249 voorkomt wordt het resultaat dus 10 records.

Als je nou deze twee statements omdraait. dan krijg je dus eerst een tijdelijke tabel in je geheugen van ya1 waarvan de voorwaarde is dat de ya1.wordnumber 249 is. Die wordt dan gekruist met het tabel waar de andere voorwaarde aan voldoet, en je krijgt opeens via een 'kortere' weg je resultaten.

Probeer eens uit te zoeken wat "EXPLAIN" precies doet, en wat het precies betekent, daarvan kan je precies zien hoe de database jouw query behandeld. Vaak kan het in een juiste volgorde zetten van de statements al redelijk veel tijd schelen.

[RvdH]

Ik ben nu aan het uittesten met de volgende query:

code:

SELECT
 B.RecordNumber
FROM
 books as B,
 yauthor as ya1,
 ytitle as yt1,
 ytitle as yt2
WHERE
 ya1.WordNumber = 249
AND
 ya1.BookNumber = B.RecordNumber
AND
 yt1.WordNumber = 4985
AND
 yt1.BookNumber = B.RecordNumber
AND 
 yt2.WordNumber = 29534
AND
 yt2.BookNumber = B.RecordNumber
AND
 (B.Cover = 1)
LIMIT 0,51;

Bedoel je zoiets? Nu selecteert ie elke row waarbij WordNumber x is, en matched die rows aan B.RecordNumber.. correct?
En op de oude manier matchte hij eerst alle rows in de tabel aan B.RecordNumber, en ging dan pas kijken of WordNumber x was?
Ik kan even niet controleren of het ene nu sneller is als het andere want ze zitten nu allebei in het geheugen

[ACM]

Op maandag 01 april 2002 22:07 schreef strlen het volgende:
Ik kan even niet controleren of het ene nu sneller is als het andere want ze zitten nu allebei in het geheugen

Gewoon mysql restarten?

Maar als ze na de eerste run (met verschillende parameters natuurlijk) kwa performance al niet meer verschillen maakt het niet zo uit gok ik
Kan best zijn dat de optimiser dat zelf al goed op pakt (wat niet zegt dat je het niet beter zo optimaal mogelijk kan aanreiken).

[RvdH]

Op maandag 01 april 2002 22:10 schreef ACM het volgende:

[..]

Gewoon mysql restarten?

Dat gaan ze niet leuk vinden op een produktie-db

Maar als ze na de eerste run (met verschillende parameters natuurlijk) kwa performance al niet meer verschillen maakt het niet zo uit gok ik
Kan best zijn dat de optimiser dat zelf al goed op pakt (wat niet zegt dat je het niet beter zo optimaal mogelijk kan aanreiken).

Nouja ik zag dus toen ik de eerste keer die query uitvoerde een tijd van 16.1 sec, en toen een half uur daarna de optimized query een tijd van 9.x seconden.. maar dit is niet echt representatief aangezien soms de mysqld heel veel te verwerken heeft en soms helemaal niets.
*zucht* deze week komt hopelijk de test-server, dan valt er hopelijk wat beter te optimizen.

[ACM]

Op maandag 01 april 2002 22:16 schreef strlen het volgende:
Dat gaan ze niet leuk vinden op een produktie-db

Och, als je gewoon tegen de mysqladmin dat ie moet restarten merken ze er geloof ik niets van, maarja...

Dat risico kan je niet zo goed nemen lijkt me

[RvdH]

Op maandag 01 april 2002 22:18 schreef ACM het volgende:

[..]

Och, als je gewoon tegen de mysqladmin dat ie moet restarten merken ze er geloof ik niets van, maarja...

Dat risico kan je niet zo goed nemen lijkt me

Er word om deze tijd dus flink gesearched in die database (http://www.ilabdatabase.com) door de amerikanen o.a., en elke keer als er een select niet plaats kan vinden komen onze results niet bovenaan (search queried meerdere db's), wat weer tot gevolg heeft dat we orders mislopen en dus geld. Elke keer als er een #55 is krijgen we een 'tijd boete', dwz we worden een half uur niet geraadpleegt. Time is money

[ACM]

Op maandag 01 april 2002 22:22 schreef strlen het volgende:
Time is money

Ok, dat risico kan je helemaal niet nemen

[jochemd]

Maar de EXPLAIN output kan je in de tussentijd wel even hier posten

[LuCarD]

Je kan ook gaan spelen met de volgorde van de tabellen. Bv. sorteren op volgorde van grootte van de tabel of result van de tabel.

[Grum]

De size van het totale aantal records in de tabel maakt niet zoveel uit als jij een goede index hebt.

De beste manier is volges mij gewoon op elke where statement een goede index te maken en gewoon kijken bij welke je het minste results terug krijgt en deze vervolgens in oplopende (maar logische) volgorde in de complete query te zetten.

[LuCarD]

Op dinsdag 02 april 2002 13:48 schreef Grum het volgende:
De size van het totale aantal records in de tabel maakt niet zoveel uit als jij een goede index hebt.

De beste manier is volges mij gewoon op elke where statement een goede index te maken en gewoon kijken bij welke je het minste results terug krijgt en deze vervolgens in oplopende (maar logische) volgorde in de complete query te zetten.

Zei ik dat dan niet?

[RvdH]

Op dinsdag 02 april 2002 13:15 schreef jochemd het volgende:
Maar de EXPLAIN output kan je in de tussentijd wel even hier posten

code:

--------------
explain
select
      b.recordnumber
from
      books as b,
      yauthor as ya1,
      ytitle as yt1,
      ytitle as yt2
where
      ya1.wordnumber = 249
and
      ya1.booknumber = b.recordnumber
and
      yt1.booknumber = b.recordnumber
and
      yt2.booknumber = b.recordnumber
and
      yt1.wordnumber = 4985
and
      yt2.wordnumber = 29534
and
      (b.cover = 1)
limit 0,51
--------------

+-------+--------+---------------------+----------+---------+----------------------+------+-------------------------+
| table | type   | possible_keys     | key  | key_len | ref         | rows | Extra           |
+-------+--------+---------------------+----------+---------+----------------------+------+-------------------------+
| yt2   | ref    | WordBook,BookNumber | WordBook |  4 | const          | 3333 | where used; Using index |
| b     | eq_ref | PRIMARY,CoverBook   | PRIMARY  |  4 | yt2.BookNumber  |    1 | where used          |
| ya1   | ref    | WordBook,BookNumber | WordBook |  8 | const,b.RecordNumber |   12 | where used; Using index |
| yt1   | ref    | WordBook,BookNumber | WordBook |  8 | const,b.RecordNumber |   12 | where used; Using index |
+-------+--------+---------------------+----------+---------+----------------------+------+-------------------------+
4 rows in set (19.77 sec)

Bye

19.77 sec.. en dat is dan de optimized query