SELECT country,province,city,isp FROM ip_address FORCE INDEX(start_end_ip) WHERE 3658152023 BETWEEN start_ip AND end_ip;
某一个IP查询IP库属于哪个城市
这条语句都要执行1秒多钟,是怎么回事啊"id" "select_type" "table" "type" "possible_keys" "key" "key_len" "ref" "rows" "Extra"
"1" "SIMPLE" " ip_address" "range" "start_end_ip" "start_end_ip" "9" \N "169538" "Using where"
这是它的执行计划
某一个IP查询IP库属于哪个城市
这条语句都要执行1秒多钟,是怎么回事啊"id" "select_type" "table" "type" "possible_keys" "key" "key_len" "ref" "rows" "Extra"
"1" "SIMPLE" " ip_address" "range" "start_end_ip" "start_end_ip" "9" \N "169538" "Using where"
这是它的执行计划
2 加一个自增长字段id作为主键
我看不出问题来~~
start_ip/end_ip都是数值型的,不是字符串型的
索引是start_ip/end_ip
表结构是:
这个问题我只要加个自增主键就OK了,就只要300毫秒左右了另外把此表分成两表start_IP=end_ip做一个表,start_IP!=end_ip的做一个表那更快了
谢谢各位的讨论。。