Performance MySQL met tabel van 1,4 miljoen records

[RAJH]

Ik ben al een aantal dagen bezig met het optimaliseren van een query. Deze query maakt gebruik van de fulltext zoek functionaliteiten van MySQL.

De tabel ziet er als volgt uit:

bedrijfsnaam	varchar(135)
bezoek_straat	tinytext
bezoek_postcode	tinytext
bezoek_plaats	tinytext

De data is 290,763 KiB groot
De index is 99,677 KiB groot
Totaal is de tabel dus 390,440 KiB groot

Deze tabel lijkt mij dus niet te groot om de uitvoertijden van de zoekqueries onder de seconde te kunnen krijgen.

Tijdens het optimaliseren ben ik tot de volgende resultaten gekomen:

id	1
select_type	simple
table	bedrijven
type	all
possible_keys	null
key	null
key_len	null
ref	null
rows	1446484
extra	using where; using filesort
opmerkingen	geen index
text query	7.0604 sec

Na toevoegen van een gecombineerde index:

id	1
select_type	simple
table	bedrijven
type	fulltext
possible_keys	bedrijfsnaam
key	bedrijfsnaam
key_len	0
ref
rows	1
extra	using where; using filesort
opmerkingen	Fulltext index op bedrijfsnaam, bezoek_straat, bezoek_postcode, bezoek_plaats
text query	1.2292 sec

De query is als volgt:

SQL:

select SQL_NO_CACHE SQL_CALC_FOUND_ROWS b.bezoek_postcode, b.bedrijfsnaam,
b.bezoek_straat, b.bezoek_plaats,
match(bedrijfsnaam, bezoek_straat, bezoek_postcode, bezoek_plaats)
against ('+amsterdam') as 'score'
FROM bedrijven b
WHERE match(bedrijfsnaam, bezoek_straat, bezoek_postcode, bezoek_plaats)
against ('+amsterdam' IN BOOLEAN MODE)
ORDER BY SCORE DESC LIMIT 0, 25

Normaal gesproken hoort er bij extra toch "using index" te staan? En is het ook normaal dat key_len op 0 blijft staan?

Tijdens het uitvoeren van de query is er constant hd gebruik, blijkbaar wordt er niet goed gebruik gemaakt van het aanwezige geheugen (query cache is uitgeschakeld om de koude uitvoertijd te kunnen meten). Ik heb al geprobeerd om de index in het geheugen te laden door gebruik te maken van "load index into cache", maar dit maakt geen verschil. De hele tabel in het geheugen plaatsen zou ik nog kunnen proberen, maar dan moet ik alle text velden omzetten naar varchar.

Heeft iemand anders nog ideeën over hoe ik deze query kan optimaliseren?

[_js_]

De index genaamd bedrijfsnaam is de gecombineerde index?

Hoe lang duurt het wanneer je die ORDER BY weglaat?

[RAJH]

De index genaamd bedrijfsnaam is inderdaad de gecombineerde index. Zonder het ORDER BY gedeelte neemt de query 1.6756 sec in beslag. Dat deze cijfers hoger uitkomen komt omdat ik vandaag een aantal zaken aan de tabelstructuur veranderd heb die niet goed uit hebben gepakt voor de snelheid, zoals tinytext naar varchar velden en een aantal veranderingen aan de instellingen van de mysql server.

Maar als ik het mij goed herinner gaf het verwijderen van de ORDER BY een snelheidswinst van rond de 100ms.

[Confusion]

Is je sort_buffer groot genoeg om het resultaat in te sorteren? Als de query veel resultaten oplevert, dan nekt de ORDER BY je, als je sort buffer te klein is.

Edit:
Hmmm... je hebt het weglaten van de order by al getimed, dan is dat het dus niet .

[ Voor 66% gewijzigd door Confusion op 07-02-2007 20:30 ]

[RAJH]

De volgende zaken m.b.t. sort vars heb ik kunnen vinden:
max length for sort data = 1MB
max sort length = 1MB
myisam max sort file size = 4MB
myisam sort buffer size = 200MB
sort buffer size = 4MB

[ACM]

Hoeveel geheugen heeft het systeem? Want als ie slechts 512MB ram heeft, dan wordt het met een dataset van meer dan 400MB erg krap om een en ander goed in het geheugen te houden natuurlijk.

Verder is er natuurlijk nog een hoop te winnen bij deze query om wat intelligenter met je zoekvelden om te gaan. Amsterdam zal bijvoorbeeld nooit in het postcode veld zitten en ik betwijfel of je uberhaupt een ander veld dan de plaatsnaam wilt doorzoeken... En dan kan je op zich zelfs met een gewone index terecht, als je accepteert dat het met 'Amsterdam' moet beginnen, en win je nog meer. Magoed, ik weet niet precies hoe dat bij mysql's full text gebeuren zit, dat heb ik eigenlijk nooit gebruikt.

[RAJH]

Het testsysteem is een AMD Athlon 64 met 1GB aan geheugen, maar het komt binnenkort op een "echte" database server te draaien.

Het is echter voor de applicatie wel noodzakelijk om door de bedrijfsnaam, bezoek_straat, bezoek_postcode en bezoek_plaats velden te kunnen zoeken. Zou het een beter idee zijn om de gehele fulltext functionaliteit van MySQL te laten vallen en bijvoorbeeld Xapian in combinatie met Omega te gaan gebruiken?

[ACM]

Nou, dat hoeft niet per se in xapian hoor. Zo groot is je dataset echt niet, maar je kan natuurlijk wel je query en applicatie intelligenter opzetten indien mogelijk.
Moet je per se een enkele term accepteren en die door alle velden heen trekken? Of mag je ook een form bouwen dat per veldtype een invoerveld toont? Want het is natuurlijk onzin om op 'amsterdam' te zoeken in de postcode en adres als iemand alleen de vestigingsplaats bedoelde. Terwijl het invoeren van een getal in het plaatsveld omdat je een postcode zoekt ook wat onzinnig is.

[RAJH]

Ik zou inderdaad wel het formulier kunnen aanpassen zodat de gebruiker door middel van een dropdown een keuze moet maken uit de zoekvelden.

Ik heb nu op de hierboven genoemde velden een apparte index gezet en zoek door deze velden met de volgende query:

SQL:

SELECT *
FROM `bedrijven`
WHERE bedrijfsnaam LIKE 'amsterdam%'
OR bezoek_straat LIKE 'amsterdam%'
OR bezoek_postcode LIKE 'amsterdam%'
OR bezoek_plaats LIKE 'amsterdam%'

Deze voert hij uit in 0.2109 sec. en het type van de explain is nu "index_merge". Ik ga nu even kijken of deze snelheid ook mogelijk is in combinatie met FULLTEXT.

SQL:

SELECT *
FROM `bedrijven`
WHERE MATCH (bedrijfsnaam) AGAINST ('amsterdam')
OR MATCH (bezoek_straat) AGAINST ('amsterdam')
OR MATCH (bezoek_postcode) AGAINST ('amsterdam')
OR MATCH (bezoek_plaats) AGAINST ('amsterdam')

Deze voert hij uit in 0.0827 sec, maar het type hievan is "ALL" en er wordt volgens de explain geen gebruik gemaakt van een index.

SQL:

SELECT SQL_NO_CACHE *
FROM `bedrijven`
WHERE MATCH (bezoek_plaats) AGAINST ('amsterdam')

Deze voert hij uit in 0.1301 sec. en het type van de explain is nu "fulltext". Het is wel vreemde dat deze query wel gebruik maakt van een index, door minder velden zoekt en toch trager is dan de query zonder index en door vier velden.

[ Voor 44% gewijzigd door RAJH op 08-02-2007 12:15 ]