On Dec 20, 2007 7:16 AM, Sharon <[EMAIL PROTECTED]> wrote: > > Baron Schwartz wrote: > > 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 > > You're right, forcing the index results in a 47 sec. query. > Any idea how to optimize this table? > 3 seconds query (not forcing the index) is way too slow.
Try InnoDB with the same primary key. This will cluster the rows together physically and *might* be faster, but it depends on your queries. Side note: be careful of making the varchar columns larger than you need, as any operations that use an in-memory temporary table (shown by "Using temporary" in EXPLAIN) will use the full length of the column, even if only a few characters are used. (The Memory storage engine doesn't support variable-length rows). -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
