List:General Discussion« Previous MessageNext Message »
From:xucheng Date:July 28 2011 12:50pm
Subject:index problem
View as plain text  
    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 : )
index problemxucheng28 Jul
  • Re: index problemJohan De Meersman28 Jul
    • Re: index problemxucheng28 Jul
  • Re: index problemRik Wasmus28 Jul