How to make MySQL use indexes in an integer range select query

Question

I'm doing a select on a table looking for rows that match between an upper and lower bound

SELECT * FROM ranges WHERE lolong <= 2091484391 AND hilong >= 2091484391 ;

the ( simplified ) create table is:

CREATE TABLE `ranges` (
  `id` bigint(10) unsigned NOT NULL AUTO_INCREMENT,
  `hi` varchar(15) DEFAULT NULL,
  `hilong` bigint(20) DEFAULT NULL,
  `lo` varchar(15) DEFAULT NULL,
  `lolong` bigint(20) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `hilong` (`hilong`),
  KEY `lolong` (`lolong`)
) ENGINE=InnoDB AUTO_INCREMENT=234447 DEFAULT CHARSET=utf8

explain shows that it's not using indexes:

mysql> explain SELECT * FROM ranges WHERE lolong <= 2091484391 AND hilong >= 2091484391 ;
+----+-------------+------------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table      | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+------------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | ranges | NULL       | ALL  | hilong,lolong | NULL | NULL    | NULL | 7232 |    24.83 | Using where |
+----+-------------+------------+------------+------+---------------+------+---------+------+------+----------+-------------+

and even when i try to force it it only uses one index

mysql> explain SELECT * FROM ranges force index (hilong,lolong) WHERE lolong <= 2091484391 AND hilong >= 2091484391 ;
+----+-------------+------------+------------+-------+---------------+--------+---------+------+------+----------+------------------------------------+
| id | select_type | table      | partitions | type  | possible_keys | key    | key_len | ref  | rows | filtered | Extra                              |
+----+-------------+------------+------------+-------+---------------+--------+---------+------+------+----------+------------------------------------+
|  1 | SIMPLE      | ranges | NULL       | range | hilong,lolong | hilong | 9       | NULL | 2757 |    65.14 | Using index condition; Using where |
+----+-------------+------------+------------+-------+---------------+--------+---------+------+------+----------+------------------------------------+

This doesn't seem right, how can I get MySQL to use both indexes, it seems like it's needlessly scanning rows for this simple looking query

Answer 1

Whenever you use AND clause and picking exact string/integer type with equals = . Its effective to use composite index.

Try having composite index on hilong and lolong. See if the query picks index, as you have two different columns in their own ranges I'm not sure if it could help.

It mostly depends upon how the app-db architecture is defined. If I were to be you.

• See if this is in transactional path or analytics path?

• See if you can identify any other AND condition that has PK/higher cardinality would reduce the range scan of the columns.

• If the table growth is limited or high, upon which you can keep the logic in Mysql if its limited and Latency with data retention is accepted by End users. If not work on other DB stores to suit its requirement in ElasticSearch/Cassandra or any DistributedSystems etc with retention.

Answer 2

Checking between two columns is essentially un-optimizable. The Optimizer does not know how many pairs will match, so it continues until the end of the table.

By having just one column, and doing a lot of extra work, it is possible to solve it. Doing it with IP Address ranges.

Answer 3

Let's rephrase the question:

SELECT * 
FROM ranges 
WHERE 2091484391 BETWEEN lolong AND hilong;

An index like:

KEY lolong_hilong (lolong, hilong)

May help to some agree