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

indexindex-tuningMySQLperformanceperformance-tuningquery-performance

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

Best Answer

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.

Related Question