I have a table of IP address ranges and their country, here is the structure:
CREATE TABLE `geoIP` ( `startBlock` int(11) unsigned NOT NULL DEFAULT '0', `endBlock` int(11) unsigned NOT NULL DEFAULT '0', `code` char(2) NOT NULL DEFAULT '', `country` varchar(60) NOT NULL DEFAULT '', KEY `startEnd` (`startBlock`,`endBlock`), KEY `country` (`country`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1;
The table is typically used to lookup the country of an IP address. I'm trying to understand why these queries do not always use the 'startEnd' index, as my slow query log is filled with entries like this:
# Query_time: 0 Lock_time: 0 Rows_sent: 1 Rows_examined: 120967 SELECT code FROM geoIP WHERE INET_ATON('220.127.116.11') > startBlock AND INET_ATON('18.104.22.168') < endBlock;
120,967 is the number of rows in the table, so it's doing a full table scan. Here's the explain for that query:
id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE geoIP ALL startEnd NULL NULL NULL 120967 Using where
Sometimes the index is used though, here's the explain for another - same query, the only difference is the IP address:
id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE geoIP range startEnd startEnd 4 NULL 9579 Using where
both queries return 1 row.
Why does MySQL only sometimes use the index? Is there anything I can do to optimise further?