Hello everyone,
I am a bit stumbled upon the following case:
I have a table with 4 values (uid, ip_start_int, ip_end_int, country) and an index (ip_start_int, ip_end_int). However, running a query with large numbers to check for, the index doesn't get used and the query ends up being 100x slower.
"SELECT country FROM static_ip_countries WHERE ip_start_int <= 3566268565 AND ip_end_int >= 3566268565"
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE static_ip_countries ALL ip_start_int NULL NULL NULL 134189 Using where
however, the same query does correctly use the index, if I do f.e.
"SELECT country FROM static_ip_countries WHERE ip_start_int <= 3568565 AND ip_end_int >= 3568565"
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE static_ip_countries range ip_start_int ip_start_int 8 NULL 1 Using where
Here is the CREATE TABLE for reference:
CREATE TABLE `static_ip_countries` (
`uid` int(10) unsigned NOT NULL AUTO_INCREMENT,
`ip_start_int` int(10) unsigned NOT NULL,
`ip_end_int` int(10) unsigned NOT NULL,
`country` int(10) DEFAULT NULL,
PRIMARY KEY (`uid`),
KEY `ip_int` (`ip_start_int`,`ip_end_int`)
) ENGINE=MyISAM AUTO_INCREMENT=134190 DEFAULT CHARSET=utf8
Why is it that the first query doesn't use the index, but the other one does?