1. Advertising
    y u no do it?

    Advertising (learn more)

    Advertise virtually anything here, with CPM banner ads, CPM email ads and CPC contextual links. You can target relevant areas of the site and show ads based on geographical location of the user if you wish.

    Starts at just $1 per CPM or $0.10 per CPC.
  2. Better Analytics for WordPress Get It Free

MySQL Index Problem

Discussion in 'MySQL' started by Clark Kent, Jun 3, 2007.

  1. #1
    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;
     
    Clark Kent, Jun 3, 2007 IP
  2. zonzon

    zonzon Peon

    Messages:
    100
    Likes Received:
    6
    Best Answers:
    0
    Trophy Points:
    0
    #2
    can you send us the explain plan of your query, did you run an optimize and analyse on your tables?
     
    zonzon, Jun 3, 2007 IP
  3. Clark Kent

    Clark Kent Guest

    Messages:
    122
    Likes Received:
    11
    Best Answers:
    0
    Trophy Points:
    0
    #3
    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 :(
     
    Clark Kent, Jun 3, 2007 IP
  4. zonzon

    zonzon Peon

    Messages:
    100
    Likes Received:
    6
    Best Answers:
    0
    Trophy Points:
    0
    #4
    which version of mysql? maybe it's the reason...
    it's hard to me to help you without testing ;)
     
    zonzon, Jun 3, 2007 IP
  5. Clark Kent

    Clark Kent Guest

    Messages:
    122
    Likes Received:
    11
    Best Answers:
    0
    Trophy Points:
    0
    #5
    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?
     
    Clark Kent, Jun 3, 2007 IP
  6. zonzon

    zonzon Peon

    Messages:
    100
    Likes Received:
    6
    Best Answers:
    0
    Trophy Points:
    0
    #6
    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....
     
    zonzon, Jun 3, 2007 IP
  7. Clark Kent

    Clark Kent Guest

    Messages:
    122
    Likes Received:
    11
    Best Answers:
    0
    Trophy Points:
    0
    #7
    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
     
    Clark Kent, Jun 3, 2007 IP
  8. zonzon

    zonzon Peon

    Messages:
    100
    Likes Received:
    6
    Best Answers:
    0
    Trophy Points:
    0
    #8
    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
     
    zonzon, Jun 3, 2007 IP
  9. zonzon

    zonzon Peon

    Messages:
    100
    Likes Received:
    6
    Best Answers:
    0
    Trophy Points:
    0
    #9
    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)
     
    zonzon, Jun 3, 2007 IP
    Clark Kent likes this.
  10. Clark Kent

    Clark Kent Guest

    Messages:
    122
    Likes Received:
    11
    Best Answers:
    0
    Trophy Points:
    0
    #10
    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.
     
    Clark Kent, Jun 3, 2007 IP
  11. zonzon

    zonzon Peon

    Messages:
    100
    Likes Received:
    6
    Best Answers:
    0
    Trophy Points:
    0
    #11
    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.... :confused:
     
    zonzon, Jun 4, 2007 IP