英文:
How to speed up country detection from ip adress query?
问题
我正在尝试使用来自以下服务的地理位置IP数据库：
https://lite.ip2location.com/database/ip-country-region-city
我将CSV文件导入了我的MySQL数据库。现在我的表格有2,716,854行。我的查询非常慢。
我的表格SQL：
CREATE TABLE `ip2location_db3`(
`ip_from` INT(10) UNSIGNED,
`ip_to` INT(10) UNSIGNED,
`country_code` CHAR(2),
`country_name` VARCHAR(64),
`region_name` VARCHAR(128),
`city_name` VARCHAR(128),
INDEX `idx_ip_from` (`ip_from`),
INDEX `idx_ip_to` (`ip_to`),
INDEX `idx_ip_from_to` (`ip_from`, `ip_to`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
我的查询：
SELECT *
FROM `ip2location_db3`
WHERE ip_from <= 1598483915
AND ip_to >= 1598483915
平均查询时间：
1.3627秒。
我的表格看起来像这样：
我如何加速这个查询？
英文:
I am trying to use geo location ip db from this service:
https://lite.ip2location.com/database/ip-country-region-city
I imported the csv file to my mysql database.Now my table has 2,716,854 rows. My queries are very slow.
My table sql:
CREATE TABLE `ip2location_db3`(
`ip_from` INT(10) UNSIGNED,
`ip_to` INT(10) UNSIGNED,
`country_code` CHAR(2),
`country_name` VARCHAR(64),
`region_name` VARCHAR(128),
`city_name` VARCHAR(128),
INDEX `idx_ip_from` (`ip_from`),
INDEX `idx_ip_to` (`ip_to`),
INDEX `idx_ip_from_to` (`ip_from`, `ip_to`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
My query:
SELECT *
FROM `ip2location_db3`
WHERE ip_from <=1598483915
AND ip_to >=1598483915
Average query time:
1.3627 seconds.
My table looks like this:
How can i speed up this query?
答案1
得分: 0
用实际需要的字段列出SELECT *，而不是SELECT *。
目前，您返回不需要的字段，比如ip_from和ip_to。
英文:
Replace SELECT * with SELECT and list fields you actually need.
Currently you are returning fields that are not required like ip_from & ip_to
答案2
得分: 0
创建数据库 ip2location;
使用 ip2location;
创建表 ip2location_db3(
ip_from INT(10) UNSIGNED,
ip_to INT(10) UNSIGNED,
country_code CHAR(2),
country_name VARCHAR(64),
region_name VARCHAR(128),
city_name VARCHAR(128),
PRIMARY KEY (ip_to)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
然后您的查询应该是:
SELECT ip_from, ip_to, country_code, country_name, region_name, city_name
FROM ip2location_db3
WHERE INET_ATON([IP地址]) <= ip_to LIMIT 1
如果您使用上述内容，您的查询速度应该会大大提高。
英文:
Your table SQL should follow their FAQ https://www.ip2location.com/faqs/db3-ip-country-region-city#database
CREATE DATABASE ip2location;
USE ip2location;
CREATE TABLE `ip2location_db3`(
`ip_from` INT(10) UNSIGNED,
`ip_to` INT(10) UNSIGNED,
`country_code` CHAR(2),
`country_name` VARCHAR(64),
`region_name` VARCHAR(128),
`city_name` VARCHAR(128),
PRIMARY KEY (`ip_to`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
Then your query should be:
SELECT `ip_from`, `ip_to`, `country_code`, `country_name`, `region_name`, `city_name`
FROM `ip2location_db3`
WHERE INET_ATON([IP ADDRESS]) <= ip_to LIMIT 1
If you use the above, your query speed should improve by quite a lot.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论