Brian Dunning <[EMAIL PROTECTED]> wrote on 11/04/2005 10:36:00 PM: > This simple find is taking 4 to 7 seconds. Way too long!! (This is a > geotargeting query using the database from IP2location.) > > select lat,lon from geocodes where ipFROM<=1173020467 and > ipTO>=1173020467 > > The database looks like this (how IP2location recommends): > > CREATE TABLE `geocodes` ( > `ipFROM` int(10) unsigned zerofill NOT NULL default '0000000000', > `ipTO` int(10) unsigned zerofill NOT NULL default '0000000000', > `lat` double default NULL, > `lon` double default NULL, > PRIMARY KEY (`ipFROM`,`ipTO`) > ) ENGINE=MyISAM DEFAULT CHARSET=latin1; > > And there are 1.7 million records. Any suggestions? > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] >
I would bet that if you do an EXPLAIN on your query that you will see that you wound up with a full table scan. It did this because it takes fewer read operations to just scan the table than if you do an indexed lookup for any more than about 30% of the rows in any table. Can you not change the query to not use <= or => ?? Shawn Green Database Administrator Unimin Corporation - Spruce Pine
