Why does MySQL only sometimes use my index for a range query?

Question

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('82.39.119.217') > startBlock AND INET_ATON('82.39.119.217') < 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?

score 4 · Accepted Answer · answered Jun 18 '12 at 01:42

The general behavior of the MySQL Query Optimizer is as follows: If less than 5% of the rows of a table has to be traversed in an index, the index is used. Otherwise, the index is dismissed and a full table scan is done.

Now look at the index: (startBlock,endBlock).

Here are two questions

How many netblock up to the third octet do you have for startBlock ?
How many netblock up to the third octet do you have for endBlock ?

To get a hold of the distribution of netblocks, run this query

SELECT *,
    IF(ABS(endNetBlock - startNetBlock) >= 256,1,0) NotWithinSameRange,
    IF(startNetBlock - endNetBlock > 0,1,0) StartRangeAheadOfEndRange
FROM
(
    SELECT COUNT(1) rangeCount,startNetBlock,endNetBlock
    FROM
    (
        SELECT
            (startBlock - MOD(startBlock,256)) startNetBlock,
            (endBlock - MOD(endtBlock,256)) endNetBlock
        FROM geoIP
    ) AA GROUP BY startNetBlock,endNetBlock
) A

This will give you a general idea of

how many netblock combinations you have
presence of any invalid ranges

If NotWithinSameRange or StartRangeAheadOfEndRange are 1 in any instance, that range has to be clenaed up. You have to be sure that all netBlocks are properly represented.

If you have StartRangeAheadOfEndRange=1, that is a definite problem.

I hope these suggestions provide food for thought.

Why does MySQL only sometimes use my index for a range query?

1 Answers1

Linked