[mysql] query met 'group by' en count() sneller krijgen

[js303]

de context
ik ben bezig met een zoekmachine en een indexeer-script, die een mysql-database met allemaal soorten informatie op woordjes indexeert en in 2 mysql-opzoektabellen wegschrijft.

tabel search_words: lijst met unieke woorden, hun soundex. bevat rond de 50.000 unieke woorden
tabel search_rel: kruistabel met koppeling naar search_words.word_id en naar bron-tabel, bron-record-id. bevat een kleine 900.000 koppelingen

het zoekscript doet bij elke zoekopdracht eerst een scan op de woorden waar de gebruiker op zoekt, en geeft vervolgens de word_id's van de gevonden woorden terug. (een combinatie van een mysql query en php verwerking - dit stuk is vooralsnog optimaal, < 0.009 seconden). vervolgens doe ik een tweetal queries op de gevonden word_id's.

Query om totaal aantal results te tellen, zodat dit over meerder pagina's verdeeld kan worden:

SQL:

SELECT
COUNT(DISTINCT srel.source_id)
FROM ws_search_rel srel
WHERE srel.word_id IN (word_id1, word_id2, word_id3)

Query om de eerste 20 results te geven, gesorteerd op aantal gevonden worden en totaalsom van gewicht van woorden:

SQL:

SELECT srel.source_id, srel.source_type, 
SUM(srel.weight) 'total_words_weights', COUNT(srel.word_id) 'unique_words_found'
FROM ws_search_rel srel 
WHERE srel.word_id IN (word_id1, word_id2, word_id3) 
GROUP BY srel.source_id 
ORDER BY unique_words_found DESC, total_words_weights DESC 
LIMIT 0,20 

de vragen
1. de 2 genoemde queries hierboven zijn erg traag, de eerste keer dat ze uitgevoerd worden. pak'em beet tussen de 1 en 9 secondes per zoekopdracht. wat ik bovendien niet snap, is dat hij na de eerste trage keer de queries wel snel zijn, rond de 0.01 en 0.1 seconde. query cache, daar zou je aanvankelijk aan denken maar feit is dat het mysql versie 3.23.49 is en die ondersteunt nog geen query cache (http://dev.mysql.com/doc/refman/4.1/en/query-cache.html).

2. mbv EXPLAIN heb ik gekeken waar de zwaktes zitten. bij de query die het totaal aantal telt, wordt wel een index gebruikt van de word_id's, maar een COUNT() op alle rijen met dezelfde word-id kan het traag maken. enige wat ik kan verzinnen is om, na het indexeren, ook per woord te calculeren hoe vaak deze wordt gebruikt en deze dan weer opslaan in de DB. is er nog een slimmere manier om deze query sneller te krijgen?

3. de 2e query, die maakt volgens EXPLAIN gebruik van een temporary en filesort tabel - snel is anders dus. probleem is dat ik gebruikmaak van GROUP BY op het veld source_id, en dat een index op dat veld weinig zin heeft en mysql een temp-table moet aanmaken. verder is het probleem dat ik wil sorteren op hoe vaak een woord in een bepaalde bron voorkomt, en wat het gewicht van dat woord ten opzichte van die bron is. die wil ik ook weer bundelen en op sorteren, dmv SUM(srel.weight) 'total_words_weights', COUNT(srel.word_id) 'unique_words_found'. aangezien deze kolommen niet eens in de DB bestaan maar berekend worden, kunnen die niet geindexeerd worden en wordt uitgeweken naar filesort.

het enige wat ik kan verzinnen, is door GROUP BY en ORDER BY te laten vallen, in plaats daarvan alles op te vragen en het complete resultaat in php te gaan groeperen, sorteren. echter, m'n result-set kan erg lang worden, zo'n 1000 - 2000 rows per zoekopdracht (sommige woorden komen namelijk in > 1000 verschillende bronnen voor). iemand tips?

[ Voor 15% gewijzigd door js303 op 09-05-2006 15:44 ]

[js303]

hmm, of m'n verhaal is saai, te lang, dom of mensen hebben er geen antwoord op...



enfin ik heb iig 1 query iets sneller gekregen, dankzij dit artikeltje, strekking ervan is dat er voor de GROUP BY een temp-table gemaakt moet worden (using temporary), en dat hoe groter de rowdata, hoe meer geheugen er nodig is.

ik heb dus 2 tekstvelden in de tabel ws_search_rel (varchar[255] extract en een varchar[255] image) uitgesplitst naar een aparte tabel die een 1:1 relatie met ws_search_rel krijgt. dit scheelt aanzienlijk: voor de opsplitsing was de totale grootte van tabel ws_search_rel 256mb, na opsplitsing nog maar 35mb.

[ Voor 88% gewijzigd door js303 op 09-05-2006 15:10 ]

[RedBeard]

Ik ben niet echt een myssql kenner, maar kun je niet zoals bij andere databases indexes aanmaken?

Dus in dit geval een index op word_id?

[chem]

Maak een temp table op basis van je zoekwoorden en ga daar vv mee aan de slag om te tellen etc. Goede kans dat dat veeeeel sneller is.

[js303]

@RedBeard : ik maak inderdaad gebruik van indexes, waaronder ook een op word_id. bij de count-query wordt daar ook gebruik van gemaakt:

SQL:

EXPLAIN 
SELECT count(DISTINCT srel.source_id)
FROM ws_search_rel srel 
WHERE srel.word_id IN (4288)

table type  possible_keys key     key_len ref  rows Extra  
srel  range word_id       word_id 3       NULL 361  where used 

@ chem: waarop zou ik de temp-table dan moeten baseren? de count-query, dat heeft toch geen zin om alle results op word_id eerst in een temptable te kwakken en vervolgens het aantal rows ervan tellen? of bedoel je dat ik van de results-query alles ongesorteerd, ongegroepeerd in een temp-table kwak en vervolgens daar weer uit ga selecteren?

[ Voor 32% gewijzigd door js303 op 09-05-2006 15:42 ]

[js303]

yes! ik ben eruit.
het is inderdaad veel sneller te krijgen met een temp-table - dank je wel voor de hint, chem! ik had de temp-table optie al overwogen maar vrij snel weer losgelaten omdat ik de verkeerde query de temptable instuurde.

na een aantal variaties, lijkt de volgende tussen-query het beste te werken:

SQL:

CREATE TEMPORARY TABLE temp_search TYPE=HEAP 
SELECT rel_id, srel.source_id, srel.source_type, sum(srel.weight) 'total_words_weights', count(srel.word_id) 'unique_words_found' 
FROM ws_search_rel srel 
WHERE srel.word_id IN (7,16415,865) 
GROUP BY srel.source_id 

kortgezegd verzamel ik - voordat ik de temp-table vul - de bronnen die gekoppeld zijn aan de 3 woorden (word_id's) en groepeer ik die per bron, bereken per bron hoeveel unieke woorden het bevat en het totaalgewicht van die woorden.

dit bleek sneller te werken dan de hele mikmap ongegroepeerd de temp-table in te schuiven. en op basis van die temp-table weer te grouperen.