RE: Help optimize this simple find

2005-11-07 Thread Gordon Bruce 
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]

Help optimize this simple find

2005-11-04 Thread Brian Dunning 
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]

Re: Help optimize this simple find

2005-11-04 Thread SGreen 
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