所以我基本上有三张桌子:

CREATE TABLE `cdIPAddressToLocation` (
  `IPADDR_FROM` int(10) unsigned NOT NULL COMMENT  Low end of the IP Address block ,
  `IPADDR_TO` int(10) unsigned NOT NULL COMMENT  High end of the IP Address block ,
  `IPLOCID` int(10) unsigned NOT NULL COMMENT  The Location ID for the IP Address range ,
  PRIMARY KEY  (`IPADDR_TO`),
  KEY `Index_2` USING BTREE (`IPLOCID`),
  KEY `Index_3` USING BTREE (`IPADDR_FROM`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

CREATE TABLE `cdIPLocation` (
  `IPLOCID` int(10) unsigned NOT NULL default  0 ,
  `Country` varchar(4) default NULL,
  `Region` int(10) unsigned default NULL,
  `City` varchar(90) default NULL,
  `PostalCode` varchar(10) default NULL,
  `Latitude` float NOT NULL,
  `Longitude` float NOT NULL,
  `MetroCode` varchar(4) default NULL,
  `AreaCode` varchar(4) default NULL,
  `State` varchar(45) default NULL,
  `Continent` varchar(10) default NULL,
  PRIMARY KEY  (`IPLOCID`)
) ENGINE=MyISAM AUTO_INCREMENT=218611 DEFAULT CHARSET=latin1;

和

CREATE TABLE  data {
  IP  varchar(50)
  SCORE  int
}

My task is to join these three tables 和 find the location data for given IP address. My query is as follows:

select 
    t.ip,
    l.Country,
    l.State,
    l.City,
    l.PostalCode,
    l.Latitude,
    l.Longitude,
    t.score
from
    (select 
        ip, inet_aton(ip) ipv, score
    from
        data
    order by score desc
    limit 5) t
        join
    cdIPAddressToLocation a ON t.ipv between a.IPADDR_FROM 和 a.IPADDR_TO
        join
    cdIPLocation l ON l.IPLOCID = a.IPLOCID

虽然这个查询很有效,但非常缓慢, 它花了大约100秒的时间才将结果归还给我的Dev盒子。

I m using mysql 5.1, the cdIPAddressToLocation has 5.9 million rows 和 cdIPLocation table has about 0.3 million rows.

When I check the execution plan, I found it s not using any index in the table cdIPAddressToLocation , so for each row in the data table it would do a full table scan against table cdIPAddressToLocation . It is very weird to me. I mean since there are already two indexes in table cdIPAddressToLocation on columns IPADDR_FROM 和 IPADDR_TO , the execution plan should exploit the index to improve the performance, but why it didn t use them.

还是我的查询有问题?

帮帮忙,多谢了