[SQL] Slow query optimaliseren, iemand een idee?

[RvdH]

Hoi,

Laat ik beginnen met te melden dat de SQL code en db ontwerp niet van mijzelf zijn, maar van een collega van mij. Wij zijn nu al een tijdje bezig de database te optimaliseren, maar zoals met bijna alles moet je problemen oplossen waar ze ontstaan -- de queries.

Dit is een query die regelmatig voorkomt in onze slow-log file:
# Time: 030307 18:50:36
# User@Host: root[root] @ db1 [212.204.245.241]
# Query_time: 16 Lock_time: 0 Rows_sent: 6 Rows_examined: 18565

SELECT B.RecordNumber FROM books AS B , ytitle AS YT1 USE INDEX (WordBook) , ytitle AS YT2 USE INDEX (WordBook) , ytitle AS YT3 USE INDEX (WordBook) WHERE YT1.BookNumber = B.RecordNumber AND YT2.BookNumber = B.RecordNumber AND YT3.BookNumber = B.RecordNumber AND YT1.WordNumber = 1242 AND YT2.WordNumber = 3455 AND YT3.WordNumber = 1299 AND B.CountryNumber = 29 LIMIT 0,51;

Zo ziet de table books eruit:

code:

+---------------+---------------------+------+-----+------------+----------------+
| Field         | Type                | Null | Key | Default    | Extra          |
+---------------+---------------------+------+-----+------------+----------------+
| RecordNumber  | int(11)             |      | PRI | NULL       | auto_increment |
| MemberNumber  | int(11)             |      | MUL | 0          |                |
| BookNumber    | varchar(20)         |      | MUL |            |                |
| Author        | varchar(60)         |      |     |            |                |
| Titel         | text                |      |     |            |                |
| Description   | varchar(100)        |      |     |            |                |
| DescripRest   | text                |      |     |            |                |
| Price         | bigint(20) unsigned |      |     | 0          |                |
| Keywords      | varchar(100)        |      |     |            |                |
| DollarPrice   | bigint(20) unsigned |      | MUL | 0          |                |
| CountryNumber | tinyint(3) unsigned |      | MUL | 0          |                |
| EntryDate     | date                |      | MUL | 0000-00-00 |                |
| Status        | char(1)             |      | MUL |            |                |
| RecordType    | tinyint(4)          |      | MUL | 0          |                |
| BookFlags     | smallint(6)         |      | MUL | 0          |                |
| Year          | smallint(6)         |      | MUL | 0          |                |
| FirstEdition  | tinyint(4)          |      | MUL | 0          |                |
| DustWrapper   | tinyint(4)          |      | MUL | 0          |                |
| Signed        | tinyint(4)          |      | MUL | 0          |                |
| Cover         | tinyint(4)          |      | MUL | 0          |                |
| SpecialField  | tinyint(4)          |      | MUL | 0          |                |
+---------------+---------------------+------+-----+------------+----------------+

en ytitle ziet er zo uit:

code:

+------------+---------+------+-----+---------+-------+
| Field      | Type    | Null | Key | Default | Extra |
+------------+---------+------+-----+---------+-------+
| WordNumber | int(11) |      | MUL | 0       |       |
| BookNumber | int(11) |      | MUL | 0       |       |
+------------+---------+------+-----+---------+-------+

En dan is er nog een tabel met woorden, en hun id is gelijk aan ytitle.wordnumber.
Wat het woord precies is, weet ik niet.. maar het zou in de beschrijving van het boek kunnen zitten ofzo.

Mijn collega weet nog niet veel van joins (alsof dat nog niet duidelijk was), en deze db-structuur is zo complex dat ik ook niet veel ideeen heb.

Iemand van jullie? Die queries moeten sneller kunnen...

[thomaske]

Ga maar eens met EXPLAIN spelen!

[cybermans]

als ik zo die query zie gebruik je voor prijzen een bigint van 20 lang (ben je zo duur dan)
maybe beter omdaar iets van een decimal (7,2) voor te gebruiken. Die join met 3x dezelfde tabel moet volgens mij ook anders kunnen want echt snel wordtie daar niet van volgens mij.

kijk ook ff hier voor meer info over het tunen van je server: http://www.mysql.com/doc/en/Server_parameters.html

[thomaske]

Ik zit nu even je datamodel te bekijken, maar zou je eens kunnen uitleggen wat het precies voorstelt, en wat je precies met je query wilt ?

[Lister]

Wat ik uit de query begrijp is dat je alle boeken zoekt die 3 specifieke woorden bevatten, klopt dat?
Kan je geen gebruik maken van subselects, dat je eerst alle boeken zoekt die het 1e woord hebben, daarin dan weer alle boeken zoekt die het 2e woord hebben en daarin dan weer alle boeken die het 3e woord hebben, dat scheelt je in ieder geval een hoop joins.

[chem]

SQL:

SELECT 
    B.RecordNumber 
FROM 
    books AS B, 
    ytitle AS YT1 USE INDEX (WordBook),
    ytitle AS YT2 USE INDEX (WordBook),
    ytitle AS YT3 USE INDEX (WordBook) 
WHERE 
    YT1.BookNumber = B.RecordNumber 
    AND YT2.BookNumber = B.RecordNumber 
    AND YT3.BookNumber = B.RecordNumber 
    AND YT1.WordNumber = 1242 
    AND YT2.WordNumber = 3455 
    AND YT3.WordNumber = 1299 
    AND B.CountryNumber = 29 
LIMIT 
    0,51;

Zo is het wat leesbaarder. Nu ff lezen, edit volgt

edit:
dus...:

SQL:

SELECT 
    B.RecordNumber 
FROM 
    books AS B, 
    ytitle AS YT
WHERE 
    YT.BookNumber = B.RecordNumber 
    AND YT.WordNumber IN (1242,3455,1299
    AND B.CountryNumber = 29 
LIMIT 
    0,51;

Lijkt me een stuk beter? Geef anders iig een EXPLAIN output. Evt. kan je de FROm omdraaien (books op ytitle ipv andersom)

[ Voor 27% gewijzigd door chem op 08-03-2003 14:28 ]

[Joror]

Chem: verschil tussen jouw query en de originele is dat de eerste 1242 EN 3455 EN 1299 doet terwijl jij 1242 OF 3455 OF 1299 doet.

Als je het op die manier doet zou je nog een GROUP BY B.RecordNumber HAVING Count(YT.WordNumber) = 3 erachter kunnen plakken, enkel weet ik bijna zeker dat dat niet sneller zou zijn

Het beste wat je zou kunnen doen is EXPLAIN gebruiken zoals als gezegd is, en je eventueel het resultaat hier geeft zodat men daarover tips kan geven.

[ Voor 22% gewijzigd door Joror op 08-03-2003 14:42 ]

[Verwijderd]

chem schreef op 08 maart 2003 @ 14:25:

SQL:

...
WHERE 
    YT1.BookNumber = B.RecordNumber 

...

Hier wordt het carthesisch produkt uitgerekend en dit maakt het geheel dus erg traag.

Herschrijf deze variant.

[ACM]

Verwijderd schreef op 08 maart 2003 @ 17:54:
Hier wordt het carthesisch produkt uitgerekend en dit maakt het geheel dus erg traag.

Herschrijf deze variant.

Doorgaans gebeurt dat niet hoor, dat wordt wel goed weggeoptimaliseerd over het algemeen.
Testen of het met (left/inner) joins beter gaat kan natuurlijk altijd.

[RvdH]

explain:

code:

+-------+--------+---------------------+----------+---------+----------------------+------+-------------------------+
| table | type   | possible_keys       | key      | key_len | ref                  | rows | Extra                   |
+-------+--------+---------------------+----------+---------+----------------------+------+-------------------------+
| YT2   | ref    | WordBook,BookNumber | WordBook |       4 | const                | 8173 | where used; Using index |
| B     | eq_ref | PRIMARY,CountryBook | PRIMARY  |       4 | YT2.BookNumber       |    1 | where used              |
| YT1   | ref    | WordBook,BookNumber | WordBook |       8 | const,B.RecordNumber |    1 | where used; Using index |
| YT3   | ref    | WordBook,BookNumber | WordBook |       8 | const,B.RecordNumber |    1 | where used; Using index |
+-------+--------+---------------------+----------+---------+----------------------+------+-------------------------+

[ACM]

code:

B     | eq_ref | PRIMARY,CountryBook | PRIMARY  |       4 | YT2.BookNumber       |    1 | where used

Ik mis daar de "using index" nogal Maar dat kan zijn omdat de B-tabel wellicht vrij klein is en een table-scan effectiever is.
Ik neem trouwens aan dat wordbook indices een gecombineerde index zijn op de booknumber + wordnumber.

Helaas is mysql's explain verder vrijwel onbruikbaar, behalve om te testen of je indices wel gebruikt worden.

Test eens het verschil met een join minder, dus alleen een YT1 en YT2 en een meer, dus YT1, 2, 3 en 4. Volgens mij is dit namelijk een van de zwakkere punten van mysql...

[ Voor 16% gewijzigd door ACM op 09-03-2003 14:35 ]