List:General Discussion« Previous MessageNext Message »
From:Rik Wasmus Date:July 28 2011 2:13pm
Subject:Re: index problem
View as plain text  
[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
Thread
index problemxucheng28 Jul
  • Re: index problemJohan De Meersman28 Jul
    • Re: index problemxucheng28 Jul
  • Re: index problemRik Wasmus28 Jul