Hi, On Dec 20, 2007 2:15 AM, Sharon <[EMAIL PROTECTED]> 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 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
