MySQL Index Problem

I have a table with ~3 million records but with original table structure mysql never uses Primary Index.
Tables has a primary key on 2 fields: PRIMARY KEY (ipFROM,ipTO)

I tried to but Indexes on ipfrom and ipto individually but still query is very slow like 5-10 seconds.

Why do you think mysql don't use primary key. If mysql can use primary key, result row will always 1 row.

Query: SELECT countryLong, ipRegion, ipCity, ipLATITUDE as lat, ipLONGITUDE as lng
FROM ip2location.ipcitylatlong
WHERE ( (INET_ATON('127.0.0.1') >= ipFrom) AND (INET_ATON('127.0.0.1') <= ipTo) )

Table: (other indexes other than primary key are added for testing)
CREATE TABLE ipcitylatlong (
ipFROM int(10) unsigned zerofill NOT NULL default '0000000000',
ipTO int(10) unsigned zerofill NOT NULL default '0000000000',
countrySHORT char(2) NOT NULL default '',
countryLONG varchar(64) NOT NULL default '',
ipREGION varchar(128) NOT NULL default '',
ipCITY varchar(128) NOT NULL default '',
ipLATITUDE double default NULL,
ipLONGITUDE double default NULL,
PRIMARY KEY (ipFROM,ipTO),
UNIQUE KEY ipFromTo (ipFROM,ipTO),
KEY NewIndex (ipFROM),
KEY IPTo_index (ipTO)
) ENGINE=MyISAM DEFAULT CHARSET=latin5;

 

can you send us the explain plan of your query, did you run an optimize and analyse on your tables?

 

Yes, I run optimize and analyse several times but no luck.

explain plan:
id~select_type~table~type~possible_keys~key~key_len~ref~rows~Extra
1~SIMPLE~ipcitylatlong~ALL~PRIMARY,ipFromTo,NewIndex,IPTo_index~NULL~NULL~NULL~2459642~Using where


I started to think that mySql doesn't use indexes if you use greater or less operators on two fields

 

which version of mysql? maybe it's the reason...
it's hard to me to help you without testing

 

mySql Version: 4.1.20

Did you use a where statement like this example with primary key on that fields and did you see that mysql uses that index. maybe 4.1.20 has a bug on this condition?

 

hummmmmm maybe the casting type of your conditions....
is mysql auto cast the 2 operands of the conditions to the good types?

try to change :
INET_ATON('127.0.0.1') >= ipFrom
--->
INET_ATON('127.0.0.1') >= INET_ATON(ipFrom)

i don't really know how mysql handle this type of data type, is it a big database or a private one for you? I'm really curious to find an issue to your problem..

If you can send me a dump....

 

Sample Data:
INSERT INTO ipcitylatlong (ipFROM, ipTO, countrySHORT, countryLONG, ipREGION, ipCITY, ipLATITUDE, ipLONGITUDE) VALUES("0000000000", "0033996343", "-", "-", "-", "-", "0", "0");
INSERT INTO ipcitylatlong (ipFROM, ipTO, countrySHORT, countryLONG, ipREGION, ipCITY, ipLATITUDE, ipLONGITUDE) VALUES("0033996344", "0033996351", "UK", "UNITED KINGDOM", "-", "-", "54.15", "-4.473");
INSERT INTO ipcitylatlong (ipFROM, ipTO, countrySHORT, countryLONG, ipREGION, ipCITY, ipLATITUDE, ipLONGITUDE) VALUES("0033996352", "0050331647", "-", "-", "-", "-", "0", "0");
INSERT INTO ipcitylatlong (ipFROM, ipTO, countrySHORT, countryLONG, ipREGION, ipCITY, ipLATITUDE, ipLONGITUDE) VALUES("0050331648", "0050331903", "US", "UNITED STATES", "MASSACHUSETTS", "BEVERLY", "42.5685", "-70.8619");
INSERT INTO ipcitylatlong (ipFROM, ipTO, countrySHORT, countryLONG, ipREGION, ipCITY, ipLATITUDE, ipLONGITUDE) VALUES("0050331904", "0050332159", "US", "UNITED STATES", "NEW JERSEY", "ATLANTIC HIGHLANDS", "40.4046", "-74.0304");
INSERT INTO ipcitylatlong (ipFROM, ipTO, countrySHORT, countryLONG, ipREGION, ipCITY, ipLATITUDE, ipLONGITUDE) VALUES("0050332160", "0050332671", "US", "UNITED STATES", "CONNECTICUT", "FAIRFIELD", "41.1862", "-73.2613");
INSERT INTO ipcitylatlong (ipFROM, ipTO, countrySHORT, countryLONG, ipREGION, ipCITY, ipLATITUDE, ipLONGITUDE) VALUES("0050332672", "0050332927", "US", "UNITED STATES", "NEW JERSEY", "LEBANON", "40.6482", "-74.817");
INSERT INTO ipcitylatlong (ipFROM, ipTO, countrySHORT, countryLONG, ipREGION, ipCITY, ipLATITUDE, ipLONGITUDE) VALUES("0050332928", "0050333695", "US", "UNITED STATES", "CONNECTICUT", "FAIRFIELD", "41.1862", "-73.2613");
INSERT INTO ipcitylatlong (ipFROM, ipTO, countrySHORT, countryLONG, ipREGION, ipCITY, ipLATITUDE, ipLONGITUDE) VALUES("0050333696", "0050333951", "US", "UNITED STATES", "MISSOURI", "CHILLICOTHE", "39.7965", "-93.4949");


IpFrom and IpTo fields are integer fields.

I already tried that combinations like this but again no luck.

explain SELECT countryLong, ipRegion, ipCity, ipLATITUDE as lat, ipLONGITUDE as lng
FROM ip2location.ipcitylatlong
WHERE ( (2130706433 >= ipFrom) AND (2130706433 <= ipTo) )

explain SELECT countryLong, ipRegion, ipCity, ipLATITUDE as lat, ipLONGITUDE as lng
FROM ip2location.ipcitylatlong
WHERE ( (ipFrom <= 2130706433) AND (ipTo >=2130706433 ) )

Explain Plan:
id~select_type~table~type~possible_keys~key~key_len~ref~rows~Extra
1~SIMPLE~ipcitylatlong~ALL~PRIMARY,ipFromTo,NewIndex,IPTo_index~NULL~NULL~NULL~2459642~Using where

 

Hum, on my side it seems to take the index....

EXPLAIN SELECT countryLong, 
       ipRegion, 
       ipCity, 
       ipLATITUDE as lat, 
       ipLONGITUDE as lng
FROM ipcitylatlong
WHERE INET_ATON('127.0.0.1') >= ipFrom
AND   INET_ATON('127.0.0.1') <= ipTo;

Code (markup):

Result:

+----+-------------+---------------+-------+--------------------------------------+------------+---------+------+------+-------------+
| id | select_type | table         | type  | possible_keys                        | key        | key_len | ref  | rows | Extra       |
+----+-------------+---------------+-------+--------------------------------------+------------+---------+------+------+-------------+
|  1 | SIMPLE      | ipcitylatlong | range | PRIMARY,ipFromTo,NewIndex,IPTo_index | IPTo_index |       4 | NULL |    1 | Using where |
+----+-------------+---------------+-------+--------------------------------------+------------+---------+------+------+-------------+

Code (markup):

My Mysql Version:
mysql-server-4.1 4.1.11a-4sarge7

 

if I recall correctly, why not using:

WHERE INET_ATON('....') BETWEEN ipFrom AND ipTo;

and you can force the use of the primary key:
FROM ipcitylatlong USE INDEX (PRIMARY)

 

IPTo_Index is useless because of record count.
If I am searching a number between 0 to 3 million, and my number is 1 million. It will search table in ~ 1 t o 2 million records and query will execute very slow.
So Primary Index is essential. Because of that original table had just PRIMARY Index as I mentioned before.
And Between was also don't use Indexes
Weird part is USE INDEX and FORCE INDEX is not working. I tried that in the first place but mysql didn't use Index.

Thanks anyway zonzon.

 

Maybe the planner estimate by the stats he gathered at the last ANALYZE that the cost of doing a sequential scan is less than index scan....