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]