From: xucheng
Date: July 28 2011 12:50pm
Subject: index problem
List-Archive: http://lists.mysql.com/mysql/225437
Message-Id: <CAAXL5M4KbSQ+2diRLhcCYPwemqLq9XCeoS106pg6TDpwaX8mEw@mail.gmail.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=UTF-8

Hi
    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 : )