[MySQL] snelheid query erg traag

[Verwijderd]

Na een middagje topics lezen op GoT, ben ik er toch nog niet uitgekomen. Probleem is als volgt:
Ik heb een database met daarin een groot (voor mijn doen dan ) aantal records; 300.000.
Kleine indicatie van de database

code:

id; int(10)
naam; varchar(60)
plaats; varchar(40)
tel; varchar(20), ivm sommige naambel-nummers geen int, bigint
website; varchar(40)

Nu is het de bedoeling dat een gebruiker kan zoeken door middel van een 'naam' en 'plaats' in te geven.vb: pietje wil graag alle bouw-bedrijven uit amsterdam.

de eerste methode hoe ik het heb geprobeerd is met like, tevens gebruikt gemaakt van 1 index op naam, 1 index op plaats, en nog een index op beide samen.
query daar bij was:

code:

SELECT * FROM `tabel` WHERE naam LIKE 'bouw%' AND plaats = 'amsterdam'

snelheid; 20 sec.

na wat searchen hier op GoT kwam ik het een en ander tegen over fulltext search, oké leuk dat dan maar even proberen...
index van plaats bleef gewoon staan
op naam werd een full-text index aangemaakt.
query hierbij was:

code:

SELECT * FROM `tabel` WHERE match (naam) against ('bouw*' IN BOOLEAN MODE) AND city = 'amsterdam'

snelheid; 16 sec.
het explain statement vertelde mij trouwens netjes dat er gebruik werd gemaakt van beide indexen

Na wat prutsen aan bovenstaande query had ik "AND city ='amsterdam' " weggehaald, gewoon om te kijken hoe snel dat ging. Wat bleek; binnen 0,02 seconde vertoonde MySQL alle bouw* bedrijven.

Dus ik denk probeer het ook even andersom, dus "match (naa ....... IN BOOLEAN MODE)" weghalen. Wat bleek hier weer, binnen een 0,02 seconde stonden alle bedrijven uit amsterdam voor mijn neus.

Maarja, dat ik alleen de bouwbedrijven uit heel nederland had, of alleen alle bedrijven uit amsterdam had ik natuurlijk niet veel aan. Dus toen de volgende query in elkaar geknutseld.
1 fulltext index voor naam
1 fulltext index voor plaats
kortom 2 maal match in 1 query.

code:

SELECT * FROM `tabel` WHERE match (naam) against ('bouw*' IN BOOLEAN MODE) AND match (plaats) against ('dongen' )

snelheid; 16 sec.

Zoals je ziet zijn de snelheden niet echt om over naar huis te schrijven. Alleen bij een aparte select zonder 'AND' werkt het allemaal wel vlotjes.

Heeft iemand enig idee, hoe deze select query sneller te maken? Of zie ik iets over het hoofd met de indexen? Opmerkingen zijn meer dan welkom

[whoami]

Ik dacht dat ik ooit eens gelezen had dat MySQL slechts 1 index kon gebruiken, is dat zo (of is dat nog altijd het geval) ?
Zoja, dan zou je kunnen overwegen om één samengestelde index te maken (dus een index op naam en plaats).

[ACM]

whoami schreef op maandag 08 augustus 2005 @ 15:51:
Ik dacht dat ik ooit eens gelezen had dat MySQL slechts 1 index kon gebruiken, is dat zo (of is dat nog altijd het geval) ?
Zoja, dan zou je kunnen overwegen om één samengestelde index te maken (dus een index op naam en plaats).

1 index per tabel in een select-statement inderdaad.

Aangezien de FTI in dit geval genoeg selectiviteit oplevert kan je een FORCE INDEX of USE INDEX-helper meegeven in je statement met de naam van je FTI. 't Lijkt me erg sterk dat je query beide indices gebruikt, je leest het resultaat van EXPLAIN vast verkeerd?

En je hoeft niet per se een FTI te gebruiken, je kan ook gewoon je eerste query gebruiken en op dat veld een gewoon index aanleggen, aangezien je bouw% doet ipv %bouw%. Let er dus op dat de 'meest selectieve index' gebruikt wordt in je statement.

[ Voor 16% gewijzigd door ACM op 08-08-2005 15:57 ]

[Genoil]

Ik denk dat dit wel eens goed kan werken:

code:

ALTER TABLE `tabel` ADD FULLTEXT (
`naam` ,
`plaats`
)

en dan je query:

code:

SELECT * FROM `tabel` WHERE match (naam,plaats) against ('bouw* +amsterdam' IN BOOLEAN MODE)

ben alleen niet 100% zeker, wel benieuwd naar het resultaat eigenlijk

[Verwijderd]

Je kan natuurlijk ook deze proberen, volgens mij gaat dat een stuk sneller..

code:

SELECT * FROM `tabel` WHERE match (naam,plaats) against ('bouw* +dongen' IN BOOLEAN MODE)

// damn, net te laat!

[ Voor 10% gewijzigd door Verwijderd op 08-08-2005 15:58 ]

[curry684]

Verwijderd schreef op maandag 08 augustus 2005 @ 15:44:
het explain statement vertelde mij trouwens netjes dat er gebruik werd gemaakt van beide indexen

Vertelde explain je nog meer? Bijvoorbeeld iets in de richting van 'using temporary'?

Iow: paste je explain results eens door, er is vaak meer aan te zien

Verwijderd schreef op maandag 08 augustus 2005 @ 15:44:

code:

SELECT * FROM `tabel` WHERE naam LIKE 'bouw%' AND plaats = 'amsterdam'

snelheid; 20 sec.

Dit geloof ik trouwens niet als je zegt dat ie alle indexen gebruikt.

Begin al eens met de meest restrictieve conditie vooraan te zetten:

code:

SELECT * FROM `tabel` WHERE plaats = 'amsterdam' AND naam LIKE 'bouw%'

Ik zie MySQL er rustig toe in staat dat ie dat verneukt anders. Ros vervolgens een index op (plaats,naam) en dit zou millisecondes moeten zijn.

[ Voor 45% gewijzigd door curry684 op 08-08-2005 16:11 ]

[Verwijderd]

Allereerst bedankt voor jullie reply's.
Heb net de optie van genoil en schutje getest, met deze query:
index stond op naam samen met plaats.

code:

SELECT naam, plaats, MATCH (naam, plaats) AGAINST ('bouw* +dongen' IN BOOLEAN MODE) AS score FROM `tabel` FORCE INDEX (idx_naam_plaats) WHERE match (naam, plaats) against ('bouw* +dongen' IN BOOLEAN MODE) ORDER BY score DESC

resultaat was redelijk bagger , eerste 2 rijen waren wel van een bouwbedrijf uit dongen, dan een paar uit andere steden.
Tevens kwamen alle bedrijven uit dongen er tussen te staan aangezien zij natuurlijk onder de match "+dongen" vallen

code:

Cube Bouw BV,  Dongen 1.3333333730698 
Delamboij Bouwadviesbureau en Timmerbedr,  Dongen 1.3333333730698 
Dongen Bouwbedrijf A D R van, Dussen 1.3333333730698 
Dongen Bouwbedrijf A D R van,  Dussen 1.3333333730698 
Dongen Bouwkundig Adviesbureau A P van,  Veenendaal 1.3333333730698 
Dongen Bouwkundig Teken- en Adviesbureau Van,  Barendrecht 1.3333333730698 
Dongen Bouwservice Van,  Oosterhout nb 1.3333333730698

iig toch bedankt, zal dadelijk even replyen op acm & curry684

[Verwijderd]

Verwijderd schreef op maandag 08 augustus 2005 @ 17:08:
Allereerst bedankt voor jullie reply's.
Heb net de optie van genoil en schutje getest, met deze query:
index stond op naam samen met plaats.

code:

SELECT naam, plaats, MATCH (naam, plaats) AGAINST ('bouw* +dongen' IN BOOLEAN MODE) AS score FROM `tabel` FORCE INDEX (idx_naam_plaats) WHERE match (naam, plaats) against ('bouw* +dongen' IN BOOLEAN MODE) ORDER BY score DESC

resultaat was redelijk bagger , eerste 2 rijen waren wel van een bouwbedrijf uit dongen, dan een paar uit andere steden.
Tevens kwamen alle bedrijven uit dongen er tussen te staan aangezien zij natuurlijk onder de match "+dongen" vallen

code:

Cube Bouw BV,  Dongen 1.3333333730698 
Delamboij Bouwadviesbureau en Timmerbedr,  Dongen 1.3333333730698 
Dongen Bouwbedrijf A D R van, Dussen 1.3333333730698 
Dongen Bouwbedrijf A D R van,  Dussen 1.3333333730698 
Dongen Bouwkundig Adviesbureau A P van,  Veenendaal 1.3333333730698 
Dongen Bouwkundig Teken- en Adviesbureau Van,  Barendrecht 1.3333333730698 
Dongen Bouwservice Van,  Oosterhout nb 1.3333333730698

iig toch bedankt, zal dadelijk even replyen op acm & curry684

Betekend inderdaad dat hij alle bouw* match en daarnaast alles met dongen.
een fultext search heeft wel meer mogelijkheden, wat betekend dat deze wel zou moeten werken.
Heb net een eigen test gedaan en het resultaat klopte wel.

code:

SELECT naam, plaats, MATCH (naam, plaats) AGAINST ('+bouw* +dongen' IN BOOLEAN MODE) AS score FROM `tabel` FORCE INDEX (idx_naam_plaats) WHERE match (naam, plaats) against ('bouw* +dongen' IN BOOLEAN MODE) ORDER BY score DESC

Wijziging is de extra + voor bouw*

[Genoil]

Verwijderd schreef op maandag 08 augustus 2005 @ 17:19:
[...]


Betekend inderdaad dat hij alle bouw* match en daarnaast alles met dongen.
een fultext search heeft wel meer mogelijkheden, wat betekend dat deze wel zou moeten werken.
Heb net een eigen test gedaan en het resultaat klopte wel.

code:

SELECT naam, plaats, MATCH (naam, plaats) AGAINST ('+bouw* +dongen' IN BOOLEAN MODE) AS score FROM `tabel` FORCE INDEX (idx_naam_plaats) WHERE match (naam, plaats) against ('bouw* +dongen' IN BOOLEAN MODE) ORDER BY score DESC

Wijziging is de extra + voor bouw*

Ja maar het probleem is dus dat "Bouwbedrijf van Dongen", uit "Dussen" ook een match is. Je hebt in deze dus weinig aan fulltext.

[Creepy]

Je zou alleen gek zijn om een fulltext search te gebruiken als dat niet nodig is. Bij like met achteraan een % is prima een index te gebruiken dus zou het opzoeken supersnel moeten gaan. In elk geval sneller dan een fuzzy fulltext search.

[Genoil]

Creepy schreef op maandag 08 augustus 2005 @ 17:32:
Je zou alleen gek zijn om een fulltext search te gebruiken als dat niet nodig is. Bij like met achteraan een % is prima een index te gebruiken dus zou het opzoeken supersnel moeten gaan. In elk geval sneller dan een fuzzy fulltext search.

Ja maar een LIKE met alleen een trailing % gaat echt geen hit opleveren bij "Dongen Bouwbedrijf A D R van"... Dat zal dan toch echt een "% bouw%" (note de spatie) moeten gebruiken, wil je alles met "bouw" en bv geen "wegenbouw". Die index gaat dus helemaal niet op...

[ Voor 26% gewijzigd door Genoil op 08-08-2005 17:39 ]

[Creepy]

True, maar aan de topicstart ze zien kan hij het met de like af en ging het puur om de snelheid, en om dan een fulltext aan te raden...

[Verwijderd]

schutje, net nog even die extra + voor bouw geprobeerd, maar bleek toch niet te werken.
Mijn eerste query was toch juist zoals ACM en curry684 al dachten.

Probleem lag denk toch aan het feit dat ik 2 aparte indexen had aangemaakt en 1 voor beide samen, toen ik bij 'explain' beide aparte indexen zag (ja het stond er echt:P) dacht ik dat alles wel goed zat, wat dus achteraf niet het geval was.

Snelheid van de query ligt nu in ieder geval overal onder de 0,2 seconde, nog niet echt snel maar het is maar op een pIII 600mhz laptopje;)

In ieder geval toch iedereen bedankt nog!

Klein volgend probleem wordt denk ik met plaatsnamen als 's-gravenhage/'s-gravenmoer, maar dit moet wel op te lossen zijn dmv te kijken of een plaatsnaam bestaat en anders een like query op %gravenmoer% uitvoeren lijkt me?!?

p.s. de reden waarom ik voor een full-text search ging was omdat ik las dat dit sneller was dan een like query, en aangezien mijn like niet vooruit te branden moest ik toch iets proberen hé

[ Voor 13% gewijzigd door Verwijderd op 08-08-2005 17:49 ]

[Woy]

Wat is het probleem met plaatsnamen zoals 's-gravenhage? als je probleem de ' is dan moet je maar een zoeken in de help naar het escapen van special characters

een like met een % aan het begin is nooit aan te raden aangezien er dan geen gebruik meer gemaakt kan worden van de index en dan alsnog alle records langsgelopen moeten worden.

[ Voor 39% gewijzigd door Woy op 08-08-2005 18:00 ]

[Genoil]

Creepy schreef op maandag 08 augustus 2005 @ 17:39:
True, maar aan de topicstart ze zien kan hij het met de like af en ging het puur om de snelheid, en om dan een fulltext aan te raden...

Ik heb geen fulltext aangeraden, ik zag alleen dat TS ermee aan het kloten was en waarom het traag was. Het feit dat namen ook vaak plaatsnamen zijn maakt het vooral overbodig. Ook schijnen hele grote recordsets niet echt lekker te gaan met fulltext. Alleen de boolean opties en de ingebouwde sortering op relevantie zijn wel relaxed.

Ik ben nu zelf bezig met een PHP implementatie van fulltext searching. Enige waar die echt traag in is is het wegen van grote hoeveelheden resultaten, maar dat valt drastisch te versnellen door niet teveel resultaten per pagina te tonen.

een like met een % aan het begin is nooit aan te raden aangezien er dan geen gebruik meer gemaakt kan worden van de index en dan alsnog alle records langsgelopen moeten worden.

Nouja dat hangt er vanaf of je nog iets van resultaten wilt hebben of alleen maar heel snel geen resultaten. Mijn custom fulltext search (die dus met LIKE %woord%) werkt, is bijna net zo snel als MySQL's fulltext (hoe meer termen hoe dichter hij er in de buurt komt), maar daarvoor moet je wel wat extra's doen...

[ Voor 24% gewijzigd door Genoil op 08-08-2005 18:18 ]

[Verwijderd]

rwb schreef op maandag 08 augustus 2005 @ 17:59:
Wat is het probleem met plaatsnamen zoals 's-gravenhage? als je probleem de ' is dan moet je maar een zoeken in de help naar het escapen van special characters

een like met een % aan het begin is nooit aan te raden aangezien er dan geen gebruik meer gemaakt kan worden van de index en dan alsnog alle records langsgelopen moeten worden.

Nou de accolades zijn niet echt het probleem, maar aangezien er straks ook door mensen vanuit buiten mij in de database gezocht kan worden bestaat er natuurlijk de mogelijkheid als:

er woont iemand in den haag, gegarandeerd dat diegene in typt "den haag" ipv " 's-gravenhage"
hetzelfde geldt bij 's-hertogenbosch, de mogelijkheid bestaat natuurlijk ook dat iemand gaat zoeken op "den bosch" ipv " 's-hertogenbosch "
hetzelfde geldt voor 's-gravenmoer een dorpje naast mijn woonplaats , dat wordt vaak gewoon geschreven als "sgravenmoer" of "s gravenmoer" etc.


Momenteel is alles ingevoerd naar aanleiding van de 'officiële plaatsnamen' die vastgesteld stonden in een formulier (html select boxje), maar tijdens het zoeken is het niet de bedoeling dat men zo'n zoekbox krijgt.

[ Voor 11% gewijzigd door Verwijderd op 08-08-2005 18:16 ]

[Genoil]

Verwijderd schreef op maandag 08 augustus 2005 @ 18:14:
[...]


Nou de accolades zijn niet echt het probleem, maar aangezien er straks ook door mensen vanuit buiten mij in de database gezocht kan worden bestaat er natuurlijk de mogelijkheid als:

er woont iemand in den haag, gegarandeerd dat diegene in typt "den haag" ipv " 's-gravenhage"
hetzelfde geldt bij 's-hertogenbosch, de mogelijkheid bestaat natuurlijk ook dat iemand gaat zoeken op "den bosch" ipv " 's-hertogenbosch "
hetzelfde geldt voor 's-gravenmoer een dorpje naast mijn woonplaats , dat wordt vaak gewoon geschreven als "sgravenmoer" of "s gravenmoer" etc.


Momenteel is alles ingevoerd naar aanleiding van de 'officiële plaatsnamen' die vastgesteld stonden in een formulier (html select boxje), maar tijdens het zoeken is het niet de bedoeling dat men zo'n zoekbox krijgt.

Je kunt zelf toch even zoeken op al die 's- plaatsen in Nederland en in je PHP code die plaatsen mappen naar spreektaal?

[Verwijderd]

uhh volgens mij heb ik toch een probleem over het hoofd gezien, namelijk als ik wil zoeken naar een bouwbedrijf met like 'bouw%' vind hij resultaten als:
- bouwbedrijf A
- bouw&infra blal
- bouwer blablbal
Maar bedrijven als onderstaande vind hij niet
- de bie bouw en grondwerk
- van der A bouwmaterialen

Nu kom ik volgens mij toch weer uit op het fulltext search verhaal, aangezien als je zoekt met %bouw% weer geen gebruikt maakt van de indexén.

[ Voor 3% gewijzigd door Verwijderd op 08-08-2005 18:34 ]

[Genoil]

nogmaals, dan moet je dus "% bouw%" oftewel "%<spatie>bouw%" gebruiken. Ja dan ben je je index kwijt. Maar ik zit nu te testen met een database van 30.000 records (de bijbel ) en de performance doet nauwelijks onder voor de fulltext variant. Dan heb ik wel een aparte kolom waar alle stopwords (noise) eruit zijn gehaald. Ik weet niet hoe goed het met 300.000 records gaat. Mag ik jouw database? ?

[Verwijderd]

Genoil schreef op maandag 08 augustus 2005 @ 18:48:
nogmaals, dan moet je dus "% bouw%" oftewel "%<spatie>bouw%" gebruiken. Ja dan ben je je index kwijt. Maar ik zit nu te testen met een database van 30.000 records (de bijbel ) en de performance doet nauwelijks onder voor de fulltext variant. Dan heb ik wel een aparte kolom waar alle stopwords (noise) eruit zijn gehaald. Ik weet niet hoe goed het met 300.000 records gaat. Mag ik jouw database? ?

oké maar is er misschien een combinatie mogelijk van fulltext search icm een index van plaatsnamen?

resultaat van % bouw%

code:

Toon Records 0 - 29 (92 totaal, Query duurde 20.7645 sec)  
SQL-query: 
SELECT * 
FROM `tabel` 
WHERE plaats = 'rotterdam'
AND naam LIKE '% bouw%'
GROUP BY telnr
ORDER BY naam
LIMIT 0 , 30

[ Voor 55% gewijzigd door Verwijderd op 08-08-2005 19:09 ]

[Genoil]

Verwijderd schreef op maandag 08 augustus 2005 @ 18:55:
[...]


oké maar is er misschien een combinatie mogelijk van fulltext search icm een index van plaatsnamen?

resultaat van % bouw%

code:

Toon Records 0 - 29 (92 totaal, Query duurde 20.7645 sec)  
SQL-query: 
SELECT * 
FROM `tabel` 
WHERE plaats = 'rotterdam'
AND naam LIKE '% bouw%'
GROUP BY telnr
ORDER BY naam
LIMIT 0 , 30

Ik vinnet maar vreemd hoor. Bij mij maakt het wel degelijk verschil:

'ft' is de volledige tekst van een bijbelvers (ongeveer 1 normale zin), maar dan helemaal in lowercase, zonder leestekens en overbodige woorden als "de, het, een , over, op" etc. (stopwords).

Zonder keys:

code:

Showing rows 0 - 29 (52 total, Query took 0.0249 sec)
SQL-query: 
SELECT *
FROM `verse`
WHERE book_name = 'Matthew'
AND ft LIKE '%god%'
LIMIT 0 , 30

Met key op book_name (en ft, maar die kun je net zo goed weglaten want dat scheelt niks ivm de %):

code:

Showing rows 0 - 29 (52 total, Query took 0.0053 sec)
SQL-query: 
SELECT *
FROM `verse`
WHERE book_name = 'Matthew'
AND ft LIKE '%god%'
LIMIT 0 , 30

Als we nou uit de losse pols eens bekijken dat jij 10 keer zoveel records hebt en een 5 keer zo trage PC (was toch een 600Mhz P3, hier staat een 2667 Mhz P4), dan gaat jouw query dus 50 keer zo traag. 0.0053 * 50 = 0,265, terwijl jouw bak er nog eens pak em beet 100 keer langer over doet....mja dat zal wel niet zo uit te rekenen zijn

Disclaimer: Ik ben geen godsdienstfanaat ofzo dat ik de bijbel gebruik als database. Het is gewoon een verdomd lekker ingedeeld groot document dat veel gedeelde onderwerpen heeft en daarom uitstekend als test-db

[ Voor 35% gewijzigd door Genoil op 09-08-2005 09:14 ]

[Bosmonster]

En met alleen een index op book_name? (of was dat je eerste voorbeeld?)

[ Voor 32% gewijzigd door Bosmonster op 09-08-2005 09:14 ]

[Genoil]

Bosmonster schreef op dinsdag 09 augustus 2005 @ 09:12:
En met alleen een index op book_name? (of was dat je eerste voorbeeld?)

Ja dat heb ik net ff ge-edit. Maakte niets uit. Dus alleen op book_name is voldoende.