[Note to self, reply to mailinglist, not to author.... ;) ]
> i found a strange problem . when i using index for 'select' , i
> got a slower result than without index .
> i have a tabe :
> create table geo_query (
> `id` int(10) unsigned not null auto_increment ,
> `start` bigint(20) unsigned not null ,
> `end` bigint(20) unsigned not null,
> `desc` varchar(1000) not null,
> primary key (`id`) ,
> key `range` (`start`,`end`)
> ) engine=myisam ;
> the whole table contains 430000 rows .
>
> 1, the query ' select * from geo_query where 1988778880 between
> start and end ;' used 0.15 second ;
> and i used 'explain' and found that it didn't use index and
> scanned the whole table .
> 2, so i changed the query for ' select * from geo_query force
> index(`range`) where 1988778880 between start and end ;' . it used
> 0.36 second .
> i can't figure it out .why the query used index spend more time than
> not ? any comment appreciate : )
The query optimizer examined your answer, and decided a full-table scan was
faster then using an index. It estimated it would require less IO operations
to read the table in sequence in this case then reading the index & fetching
the appropriate records from the table. Turned out if was right. This is often
the case when large portions of a table (or index) could possibly matched by
the first guess. Here, the first 'guess' is that everything below start =
1988778880 is a possible match (as it's first field of the index `range`).
Every one of them has to be verified of having an `end` > your number, and has
to fetch the appropriate record if it does which is costly in harddisk IO.
BTW: as this looks as a GeoIP query, based on IP, if the `start` & `end`
ranges cannot overlap, this is probably faster:
SELECT * FROM geo_query
WHERE 1988778880 > start
ORDER BY start DESC LIMIT 1.
--
Rik Wasmus
