Hi,

On Dec 20, 2007 2:15 AM, Sharon <talsharon@hotmail.com> wrote:
> Hello all,
> Given this table:
> DROP TABLE IF EXISTS `maprimary`.`tbl_locales_ip2l`;
> CREATE TABLE `maprimary`.`tbl_locales_ip2l` (
> `ipStart` int(10) unsigned zerofill NOT NULL default '0000000000',
> `ipEnd` int(10) unsigned zerofill NOT NULL default '0000000000',
> `countryCode` varchar(2) default NULL,
> `country` varchar(100) default NULL,
> `state` varchar(100) default NULL,
> `city` varchar(120) default NULL,
> `lat` float NOT NULL default '0',
> `lon` float NOT NULL default '0',
> `zipCode` varchar(10) NOT NULL default '0',
> `timeZone` int(10) NOT NULL default '0',
> PRIMARY KEY USING BTREE (`ipStart`,`ipEnd`)
> ) ENGINE=MyISAM DEFAULT CHARSET=utf8;
>
> When I use this query:
> SELECT * FROM `tbl_locales_ip2l` WHERE `ipStart` <= 3741319167 AND
> `ipEnd` >= 3741319167;
> I can see that the primary key is not used and the query takes about 3 sec.
> But when I use this query:
> SELECT * FROM `tbl_locales_ip2l` WHERE `ipStart` <= 374131916 AND
> `ipEnd` >= 374131916;
> The primary key is used.
> The table contains about 3M rows.
> Can anyone explain?
> Thanks, Sharon.

if the query will access more than a certain amount of rows, it won't
be used. There is a set of heuristics for this; the actual number
varies, but people often say a full scan is about as much work as an
index scan that retrieves 30% of the rows. That's not quite the way
the optimizer works, but it gives you an idea.

If you think it really will be faster, use USE INDEX or FORCE INDEX and see.

Baron