[MySQL] hangt bij fulltext search op bepaald woord

[Juup]

Bij de licht-overbelaste webserver van een klant van ons treed het volgende fenomeen op:

Normale query, snel klaar:

code:

mysql> SELECT visitor_id FROM CVDoc WHERE visitor_id < 100 AND match(doc,summary,freetext,key_accomplishments) against ('Monkey');
Empty set (0.39 sec)

Supertrage, hangende query:

code:

mysql> SELECT visitor_id FROM CVDoc WHERE visitor_id < 100 AND match(doc,summary,freetext,key_accomplishments) against ('Project');
Empty set (3 min 31.34 sec)

Ik snap niet waarom het zoeken op het woord "Project" het zo rete traag maakt.

[Juup]

Dit is de table:

code:

mysql> DESCRIBE CVDoc;
+---------------------+---------+------+-----+---------+-------+
| Field               | Type    | Null | Key | Default | Extra |
+---------------------+---------+------+-----+---------+-------+
| visitor_id          | int(11) |      | PRI | 0       |       |
| doc                 | text    | YES  | MUL | NULL    |       |
| summary             | text    | YES  |     | NULL    |       |
| freetext            | text    | YES  |     | NULL    |       |
| key_accomplishments | text    | YES  |     | NULL    |       |
+---------------------+---------+------+-----+---------+-------+

[Voutloos]

hoeveel id's krijg je terug bij beide queries als je die id voorwaarde weglaat?

[Juup]

Voutloos schreef op donderdag 17 mei 2007 @ 21:50:
hoeveel id's krijg je terug bij beide queries als je die id voorwaarde weglaat?

Die durf ik niet te draaien. Daar doet hij 1324554 jaar over.

[avon]

Lijkt me dat je je my.ini even wat moet aanpassen op geheugen gebruik!

Kan je die is posten?

[Voutloos]

Welnee, gewoon Count(*) met alleen die fulltext clause zal sneller zijn dan 3 minuten.

[Juup]

Nog gekker: deze query duurt maar 0.36 sec:

code:

SELECT visitor_id, match(doc,summary,freetext,key_accomplishments) against ('Project') FROM CVDoc WHERE visitor_id < 100;

[Voutloos]

Nee, dat is niet gek. Een fulltext where clause wordt altijd als eerste gedaan, ook al zou visitor_id nu een betere index zijn. Vandaar mijn vraag ook.

[Juup]

Het lijkt wel alsof eerst de match word gedraaid op alle rows en daarna de visitor_id constraint pas wordt gedaan.

Edit: Aha Voutloos heeft het antwoord. Is er een manier om dat te omzeilen?

[ Voor 26% gewijzigd door Juup op 17-05-2007 21:54 ]

[Juup]

Voutloos:

code:

mysql> SELECT count(*) FROM CVDoc WHERE match(doc,summary,freetext,key_accomplishments) against ('Project');
+----------+
| count(*) |
+----------+
|        0 |
+----------+
1 row in set (1 min 44.05 sec)

Alleen in boolean mode geeft hij results:

code:

mysql> SELECT count(*) FROM CVDoc WHERE match(doc,summary,freetext,key_accomplishments) against ('Project' in boolean mode);
+----------+
| count(*) |
+----------+
|   123438 |
+----------+
1 row in set (1 min 23.41 sec)

[ Voor 30% gewijzigd door Juup op 17-05-2007 22:06 ]

[Verwijderd]

MySQL kan maar 1 index gebruiken per tabel in een query. In dit geval gebruikt hij dus alleen de fulltext index om te zoeken en niet de index op id.

Je zou dit eventueel met een subselect kunnen oplossen.

SQL:

SELECT visitor_id 
FROM CVDoc 
WHERE visitor_id < 100 
AND visitor_id IN (
 SELECT visitor_id 
 FROM CVDoc 
 WHERE match(doc,summary,freetext,key_accomplishments) against ('Project')
)

[ripexx]

Voordat je gaat optimaliseren bekijk je natuurlijk eerst de EXPLAIN Daarna kan je gaan kijken hoe je gaat optimaliseren. Dus voer beide queries aan de DB met EXPLAIN ervoor en kijk of daar al verschillen zitten. Daarna kan je extra voorwaarden gaan toepassen. Verder is je MySQL versie ook van belang als je zaken als sub queries wil draaien.

Overigens zou ik bovenstaande query om draaien. Je gaat volgens mij nu eerst een fulltext scan doen op de gehele tabel en daarna de recordset beperken op de ID.

[_Sunnyboy_]

De query die ik in dit geval zou gebruiken:

SQL:

SELECT visitor_id
FROM (
  SELECT visitor_id
  FROM CVDoc
  WHERE visitor_id < 100
)
WHERE match(doc,summary,freetext,key_accomplishments) against ('Project') 

Kwestie van kijken welke het snelste is