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

  • 1
    This is not actually a range select... you put a lower condition on one variable and an upper one in another different variable... That ain't easy for any database. – joanolo Apr 24 '17 at 6:59

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.

  • Only in rare cases can more than one "range" be handled by a composite index. – Rick James Apr 28 '17 at 1:33

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.

Your Answer

 
discard

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.