我有一个150万条记录的表(ip2country),我正在执行以下查询,该查询需要4秒钟以上的时间
我的表结构

CREATE TABLE `ip2country` (
  `beginIPNum` bigint(20) DEFAULT NULL,
  `endIPNum` bigint(20) DEFAULT NULL,
  `countryId` varchar(4) DEFAULT NULL,
  `countryName` varchar(50) DEFAULT NULL,
  `state` varchar(50) DEFAULT NULL,
  `city` varchar(50) DEFAULT NULL,
  KEY `index1` (`beginIPNum`,`endIPNum`) USING BTREE
) ENGINE=MyISAM DEFAULT CHARSET=utf8
PARTITION BY RANGE  COLUMNS(beginIPNum,endIPNum)
(PARTITION p0 VALUES LESS THAN (16777216,16777216) ENGINE = MyISAM,
 PARTITION p1 VALUES LESS THAN (251658240,251658240) ENGINE = MyISAM,
 PARTITION p2 VALUES LESS THAN (503316480,503316480) ENGINE = MyISAM,
 PARTITION p3 VALUES LESS THAN (754974720,754974720) ENGINE = MyISAM,
 PARTITION p4 VALUES LESS THAN (1006632960,1006632960) ENGINE = MyISAM,
 PARTITION p5 VALUES LESS THAN (1258291200,1258291200) ENGINE = MyISAM,
 PARTITION p6 VALUES LESS THAN (1509949440,1509949440) ENGINE = MyISAM,
 PARTITION p7 VALUES LESS THAN (1761607680,1761607680) ENGINE = MyISAM,
 PARTITION p8 VALUES LESS THAN (2013265920,2013265920) ENGINE = MyISAM,
 PARTITION p9 VALUES LESS THAN (2264924160,2264924160) ENGINE = MyISAM,
 PARTITION p10 VALUES LESS THAN (2516582400,2516582400) ENGINE = MyISAM,
 PARTITION p11 VALUES LESS THAN (2768240640,2768240640) ENGINE = MyISAM,
 PARTITION p12 VALUES LESS THAN (3019898880,3019898880) ENGINE = MyISAM,
 PARTITION p13 VALUES LESS THAN (3271557120,3271557120) ENGINE = MyISAM,
 PARTITION p14 VALUES LESS THAN (3523215360,3523215360) ENGINE = MyISAM,
 PARTITION p15 VALUES LESS THAN (3774873600,3774873600) ENGINE = MyISAM,
 PARTITION p16 VALUES LESS THAN (4294967295,4294967295) ENGINE = MyISAM)

SELECT beginIPNum,endIPNum,countryId,countryName
FROM sdportallog.ip2country
WHERE 2130706433 BETWEEN beginIPNum AND endIPNum


谁能帮我吗?

最佳答案

尝试

SELECT beginIPNum,endIPNum,countryId,countryName
FROM sdportallog.ip2country
WHERE beginIPNum  <= 2130706433 AND endIPNum >= 2130706433

10-07 14:53
查看更多