[MySQL] Slome query met <= vergelijking

[Mikey NL]

Ik ben bezig met het optimaliseren van een website en loop nu tegen een vervelend probleem aan.

Ik heb een tabel in een MySQL database (5.0.77) die bestaat uit een aantal kolommen (namen zijn in echt datamodel anders):

code:

Table blaat:
- test1_id: int(4)
- test2_id: int(4)
- test3_id: int(4)
- test4_id: int(4)
- description: varchar(254)
- test_value: int(7)

PRIMARY KEY bestaat uit de velden test1_id, test2_id, test3_id en test4_id

De tabel bevat zo'n 2,5 miljoen regels en de kolom value heeft momenteel waarden tussen de 0 en 151111
Nu probeer ik hier een simpele query op uit te voeren:

code:

SELECT DISTINCT test3_id FROM blaat WHERE test_value <= 90 ORDER BY test3_id;

Het uitvoeren van deze query duurt zo'n 1,15 seconden, veels te lang natuurlijk.
Uiteraard even geprobeerd om een index aan te leggen, eerst op het veld test_value, maar volgens een EXPLAIN op die query wordt er dan geen gebruik van gemaakt.
Vervolgens een index aangelegd op test_value en test3_id samen, zodat alle data uit de index kan komen, maar dit zorgt vreemd genoeg voor een toename in de tijd (1,85s).

Als ik samen met die laatste index de volgende query uitvoer, dan is deze wel onmiddelijk klaar (0,01s)

code:

SELECT DISTINCT test3_id FROM blaat WHERE test_value = 90 ORDER BY test3_id;

Ik zou er bijna over nadenken om gewoon 100x die query uit te voeren, aangezien dat sneller is dan een query met een <= vergelijking erin.

Mis ik nu iets vreemds hier? Een index zou toch prima om moeten kunnen gaan met <= vergelijkingen (het is een BTREE index trouwens).

[Keiichi]

Staat je query cache aan of uit? Om dit goed te testen moet het uit staan.

Ik vind het niet gek dat een query lang duurt, er moet immers een full table scan gedaan worden voor de clausule WHERE test_value <= 90. Bij '<=' helpt het zijn van een index er niet aan (Ik kan niet zo snel vertellen waarom niet precies, maar hiervoor zijn indexes niet bedoeld)

[Mikey NL]

Query cache staat aan, maar bij herhaaldelijk testen zowel met als zonder indexen geeft zelfde resultaat, ook na een flush van de cache.

En volgens de MySQL documentatie zou de index wel degelijk moeten helpen (link):

B-Tree Index Characteristics

A B-tree index can be used for column comparisons in expressions that use the =, >, >=, <, <=, or BETWEEN operators.

Maar goed, zou er dan een betere manier zijn om dit op te lossen, lijkt me ook niet ok om alle 2,5 miljoen records naar PHP te trekken en daar te gaan filteren :-)

[Keiichi]

Mikey NL schreef op vrijdag 01 april 2011 @ 09:36:
Maar goed, zou er dan een betere manier zijn om dit op te lossen, lijkt me ook niet ok om alle 2,5 miljoen records naar PHP te trekken en daar te gaan filteren :-)

Iets zegt me dat je wat langer dan 2 seconden bezigt bent dan.

* Keiichi gaat zichzelf eens wat meer inlezen over indexes.

[ACM]

Keiichi schreef op vrijdag 01 april 2011 @ 09:22:

Ik vind het niet gek dat een query lang duurt, er moet immers een full table scan gedaan worden voor de clausule WHERE test_value <= 90. Bij '<=' helpt het zijn van een index er niet aan (Ik kan niet zo snel vertellen waarom niet precies, maar hiervoor zijn indexes niet bedoeld)

Zo simpel is het niet. Maar zoals altijd is de tijd voor een query heel gevoelig voor het aantal records dat daadwerkelijk bekeken moet worden.

Als er bijvoorbeeld bijna 100% van de records een waarde kleiner dan 90 heeft is het compleet zinloos om daar een index voor te gebruiken, als 1% van de records kleiner is dan 90 is het wel degelijk zinnig. Sterker nog, in het eerste geval is een index puur op test3_id (of test3_id, test_value misschien) wellicht nuttiger omdat MySQL dan eerst kan sorteren en daarna pas filteren.
En dat bepaald uiteraard ook of het 'veels te lang' is om er 1.15 seconde over te doen.

Mikey NL schreef op vrijdag 01 april 2011 @ 09:19:
[code]
Table blaat:
- test1_id: int(4)
- test2_id: int(4)
- test3_id: int(4)
- test4_id: int(4)
- description: varchar(254)
- test_value: int(7)

Het gebruiken van de lengte-specificatie voor getallen is compleet zinloos in MySQL, iig voor performance-doeleinden. Als je maximaal 9999 in een veld wil opslaan, gebruik dan een smallint. Die neemt daadwerkelijk maar 2 bytes in opslag in. Een int(7) past waarschijnlijk net in een mediumint.

Dat maakt wel uit voor de grootte van met name je primary key en zou je query kunnen versnellen.

Overigens is 90 queries uitvoeren met de waarden 1 t/m 90 niet een equivalent voor deze ene trage query. Als je geen 'distinct' had staan uiteraard wel.

[MueR]

Kijk bij je query cache in ieder geval even naar welke instellingen daarvoor staan. De MySQL defaults zijn ongeveer zo oud als de x86 processoren, dus heel veel doet dat niet.