Snelheid mysql query

foondump ontwikkeling
#1

Beste,

Allereerst bedankt voor het gratis gebruiken van de foondump tool. Tijdens het ontwikkelen van mijn eigen zoek versie loop ik toch een probleem tegen het lijf.
Namelijk de snelheid waarmee mysql de volgende query afhandelt:

SELECT count(*) FROM `pink_subscriber` WHERE lastname LIKE '%$naam%' and city like '%$plaats'

Dit duurt namelijk de eerste keer een 16 seconde. De pc is ook niet de allersnelste, maargoed je zou toch verwachten dat het iets sneller moet kunnen.

Een query als deze werkt weer gewoon vlot:

SELECT * FROM `pink_subscriber` WHERE lastname LIKE '%$naam%' and city like '%$plaats' LIMIT 10,10

Ook heb ik met php al gewerkt met php_affected_rows/php_count_rows op de query “select * from”, maar dit gaat ook geen tientallen secondes sneller.

Heeft iemand een idee hoe ik die query sneller kan maken, door er bijv een index ofzo bij te zetten. Zo ja waar moet die index dan op staan, aangezien ik normaal gezien weinig met indexes werk.

Alvast bedankt,
Bas

#2

Hallo Bas,

Het probleem is dat wanneer je % aan het begin van een char veld gebruikt, er geen indexen meer gebruikt kunnen worden op die kolommen.

Go figure: wanneer je op %ansen% zoekt kan je zowel aansens bedoelen als jansen als zansend. Kortom, een zogenaamde ‘full table scan’ is het enige wat erop zit: de database server moet de hele tabel doorzoeken!! (Vergelijk: wanneer je op j%nsen zoekt hoeft de database in ieder geval alleen de records die met een j beginnen te bekijken).

De LIMIT query werkt vlot omdat MySQL zo slim is om na 10+10=20 resultaten ermee op te houden. Maar wanneer je een ORDER BY toevoegt - zelfs op een gesorteerde tabel - dan is de winst weer weg, omdat eerst de resultaten worden opgehaald en dan gesorteerd.

De oplossing: gebruik nooit % aan het begin van een zoekterm.
Waarom ook eigenlijk? Ik zie echt niet wat je met %$plaats wil bereiken…

#3

bedankt voor je antwoord rgj.

Een vb’tje van een plaats: 's-gravenmoer/'s-gravenhage.
waarschijnlijk typen de mensen dat niet letterlijk in.

waarschijnlijk is het dus alleen slim om (als ze eventueel nodig zijn) de wildcharts achteraan de zoekopdracht te plaatsen?

Is het dan misschien ook slim om ipv van 2 enkele index’en 1 van beide te maken?

Alvast bedankt.

#4

probleem is inmiddels als volgt opgelost:

query voor city gebruikt geen wildcharts meer, voor lastname wel.

SELECT * FROM `pink_subscriber` WHERE lastname like '%boek%' and city = 'amsterdam'

Er wordt gekeken of de plaatsnaam bestaat, anders wordt er dmv MATCH statement in SQL een andere plaats als keuze aangewezen.

Nogmaals bedankt!