[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 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=arch...@jab.org
