[mysql 5] optimisation for small indexes

[jptsetung]

Hi,

I warn you this is a very very strange problem.
A problem of slowness with one of my indexes. Let me explain.

I have a table users with about 100,000 entries.


There is a field state, that is a int(4) and that has 3 different values.
There is an index on this field.


The following request runs in about 1 second (!)

SELECT id
FROM users
WHERE state > '0'
ORDER BY id DESC 
LIMIT 1

I have another field birthyear that is a int(11) with no index on it.

The following request (almost the same with a nex condition) runs in less than 0.001 second (!)

SELECT id
FROM users
WHERE birthyear > '1980'
ORDER BY id DESC 
LIMIT 1

I told you, this is strange ...

[rmiao]

Did you report it to MySql vendor?

[jptsetung]

Hi, didn't know how to do. How can I report to mysql vendor ?

I have run an EXPLAIN on the 2 queries in case that can help to understand :

slow one :

id 1
select_type SIMPLE
table users
type range
possible_keys state
key state
key_len 4
ref NULL
rows 79245
Extra Using where; Using filesort

quick one

id 1
select_type SIMPLE
table users
type index
possible_keys NULL
key PRIMARY
key_len 4
ref NULL
rows 84197
Extra Using where

[jptsetung]

Well I think I have solved the problem after reading this doc :

http://dev.mysql.com/doc/refman/5.0/...imization.html


Now the querie runs wihtin 0.003 second!
Only by adding FORCE INDEX ( PRIMARY )

SELECT id
FROM users
FORCE INDEX ( 
PRIMARY ) 
WHERE state < '0'
ORDER BY id DESC 
LIMIT 1