Is it possible to change the geocodes table to look like CREATE TABLE `geocodes` ( `ip` int(10) unsigned zerofill NOT NULL default '0000000000', `lat` double default NULL, `lon` double default NULL, PRIMARY KEY (`ip`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1;
Then you could do SELECT lat,lon FROM geocodes WHERE ip BETWEEN 1173020467 AND 1173020467 ; -----Original Message----- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Friday, November 04, 2005 10:33 PM To: Brian Dunning Cc: mysql@lists.mysql.com Subject: Re: Help optimize this simple find 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 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
