RE: Help optimize this simple find
Is it possible to change the geocodes table to look like CREATE TABLE `geocodes` ( `ip` int(10) unsigned zerofill NOT NULL default '00', `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 '00', >`ipTO` int(10) unsigned zerofill NOT NULL default '00', >`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]
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 '00', >`ipTO` int(10) unsigned zerofill NOT NULL default '00', >`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
Help optimize this simple find
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 '00', `ipTO` int(10) unsigned zerofill NOT NULL default '00', `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]