I got issues with some geo IP query. So basically here is create code of table:
CREATE TABLE `geo_ip_city` ( `id` INT(5) UNSIGNED NOT NULL AUTO_INCREMENT, `begin` BIGINT(20) NOT NULL DEFAULT '0', `end` BIGINT(20) NOT NULL DEFAULT '0', `code` VARCHAR(2) NOT NULL DEFAULT '', `city` TINYINT(3) UNSIGNED NOT NULL DEFAULT '0', PRIMARY KEY (`id`), INDEX `begin` (`begin`), INDEX `end` (`end`), INDEX `code` (`code`) )
SELECT * FROM `geo_ip_city` USE INDEX ( `end` ) WHERE `begin` <= 2523596988 AND `end` >= 2523596988 LIMIT 1
Table size is about 4.5m records.
This query runs when all other criteria doesn't work, but still on peak times quite often.
In worst case scenario (where nothing found) it executes for 1.9444 seconds.
Here is explain:
id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE geo_ip_city range end end 8 NULL 2297506 Using index condition; Using where
So when i have alot of requests for this feature, my servers going crazy.
Can I do something with this table or query to increase performance (maybe partitioning or complex keys)? Or should I watch in some other way?
LIMIT 1? If you remove it, is there a chance that you get more than 1 row?
(begin, end)overlapping or not?
LIMIT 1here to prevent scanning table if result already found.