我有一个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