MySQL: index for query with range and order by

user_5402501 0
Viewed 239

I'm trying to get a query containing a range and ORDER BY ... DESC to use an index.
The index is used if I remove the ORDER BY and just use the range.
The index is also used if I remove the range and just use the ORDER BY.

Table:

CREATE TABLE `messages` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `service` varchar(260) COLLATE utf8mb4_unicode_ci NOT NULL,
  `time` datetime NOT NULL,
  `country` varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL,
  `city` varchar(100) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `country_code` char(2) COLLATE utf8mb4_unicode_ci NOT NULL,
  `issue` varchar(50) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `latitude` double DEFAULT NULL,
  `longitude` double DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `service_time_idx` (`service`,`time`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci

Query with range and ORDER BY (Not using index):

MariaDB [m]> explain select service, time, country, city, country_code,
 issue, latitude, longitude
   from messages
   where service = 'myservice'
     and time BETWEEN DATE_SUB( NOW() , INTERVAL 24 HOUR ) AND NOW()
   order by time desc;
+------+-------------+---------------------------+-------+------------------+------------------+---------+------+------+-------------+
| id   | select_type | table                     | type  | possible_keys    | key              | key_len | ref  | rows | Extra       |
+------+-------------+---------------------------+-------+------------------+------------------+---------+------+------+-------------+
|    1 | SIMPLE      | messages                  | range | service_time_idx | service_time_idx | 1047    | NULL |    1 | Using where |
+------+-------------+---------------------------+-------+------------------+------------------+---------+------+------+-------------+

Query just using range (using index).

MariaDB [m]> explain select service, time, country, city, country_code,
 issue, latitude, longitude
   from messages
   where service = 'myservice'
     and time BETWEEN DATE_SUB( NOW() , INTERVAL 24 HOUR ) AND NOW();
+------+-------------+---------------------------+-------+------------------+------------------+---------+------+------+-----------------------+
| id   | select_type | table                     | type  | possible_keys    | key              | key_len | ref  | rows | Extra                 |
+------+-------------+---------------------------+-------+------------------+------------------+---------+------+------+-----------------------+
|    1 | SIMPLE      | messages                  | range | service_time_idx | service_time_idx | 1047    | NULL |    1 | Using index condition |
+------+-------------+---------------------------+-------+------------------+------------------+---------+------+------+-----------------------+
1 row in set (0.004 sec)

Query just using ORDER BY (using index):


MariaDB [m]> explain select service, time, country, city, country_code, issue, latitude, longitude
   from messages
   where service = 'myservice'
     and time = '2020-10-03 09:51:25'
   order by time desc;
+------+-------------+---------------------------+------+------------------+------------------+---------+-------------+------+-----------------------+
| id   | select_type | table                     | type | possible_keys    | key              | key_len | ref         | rows | Extra                 |
+------+-------------+---------------------------+------+------------------+------------------+---------+-------------+------+-----------------------+
|    1 | SIMPLE      | messages                  | ref  | service_time_idx | service_time_idx | 1047    | const,const |    1 | Using index condition |
+------+-------------+---------------------------+------+------------------+------------------+---------+-------------+------+-----------------------+
1 row in set, 1 warning (0.001 sec)

Oddly, I can get the query to work with both the range and ORDER BYif I only select on the service and time columns:

MariaDB [m]> explain select service, time from messages
   where service = 'myservice'
     and time BETWEEN DATE_SUB( NOW() , INTERVAL 24 HOUR ) AND NOW()
   order by time desc;
+------+-------------+---------------------------+------+------------------+------------------+---------+-------+------+--------------------------+
| id   | select_type | table                     | type | possible_keys    | key              | key_len | ref   | rows | Extra                    |
+------+-------------+---------------------------+------+------------------+------------------+---------+-------+------+--------------------------+
|    1 | SIMPLE      | messages                  | ref  | service_time_idx | service_time_idx | 1042    | const |    1 | Using where; Using index |
+------+-------------+---------------------------+------+------------------+------------------+---------+-------+------+--------------------------+
1 row in set (0.002 sec)

MariaDB [m]> EXPLAIN FORMAT=JSON select service, time, country, city, country_code, 
issue, latitude, longitude 
  from messages 
where service = 'myservice' 
  and time BETWEEN DATE_SUB( NOW() , INTERVAL 24 HOUR ) AND NOW() 
order by time desc;

{
  "query_block": {
    "select_id": 1,
    "table": {
      "table_name": "messages",
      "access_type": "range",
      "possible_keys": ["service_time_idx"],
      "key": "service_time_idx",
      "key_length": "1047",
      "used_key_parts": ["service", "time"],
      "rows": 1,
      "filtered": 100,
      "attached_condition": "messages.service = 'myservice' and messages.`time` between <cache>(current_timestamp() - interval 24 hour) and <cache>(current_timestamp())"
    }
  }
}

How can I get the first query above to use the index?
Obviously I can write the query a different way if I need to.

mariadb where-clause sql-order-by query-optimization mysql
2 Answers

Your INDEX(service, time) is perfect. EXPLAIN is somewhat cryptic; Let me elaborate;

(But first, time is a DATETIME, so time = '12:10' does not make sense.)

So that we can see them all at once:

1  where service = 'myservice'
     and time BETWEEN DATE_SUB( NOW() , INTERVAL 24 HOUR ) AND NOW()
   order by time desc;

2  where service = 'myservice'
     and time BETWEEN DATE_SUB( NOW() , INTERVAL 24 HOUR ) AND NOW();

3  where service = 'myservice'
     and time = '2020-10-03 09:51:25'
   order by time desc;

4  where service = 'myservice'
     and time BETWEEN DATE_SUB( NOW() , INTERVAL 24 HOUR ) AND NOW()
   order by time desc;

What happens when you change #3 to test against a datetime?

All of them have

   where service = 'myservice'

so it is good to have INDEX(service, ...)

All of them then have a reference, either via a "range" or "=", to time, so that is a good column to have next: INDEX(service, time)

Now, let's see if the ORDER BY (where present) can take advantage of the INDEX(service, time).

  • The entire WHERE has been handled, so it is possible to move onto GROUP BY (not present in your case) and ORDER BY.
  • EXPLAIN give no clue of whether it is used or not. (Or at least not a definitive clue)
  • EXPLAIN FORMAT=JSON will give a strong clue. Run that.
  • I will say that in all of your ORDER BY cases, the index will be used.
  • Here's a technique for determining it by counting the rows touched: http://mysql.rjweb.org/doc.php/index_cookbook_mysql#handler_counts

Perhaps this is confusing things:

  • Extra = Using index says that the index is "covering" which means that all the columns anywhere in the SELECT are included in the INDEX(...).
  • Extra = Using index condition refers to "ICP", which is mostly irrelevant to this discussion.
  • Neither of the above in Extra -- this does not say anything about whether some INDEX is being used.
  • Type=All (which you don't have) means that no INDEX is used. Note: The optimizer sometimes shuns all indexes and chooses to read "All" rows.
  • Type other than All means that an index was at least partially used.
user_17668310

Your query SHOULD be good for the index. The primary condition of the service is the lead element of the index, then the time secondary. So your query should be optimized from that. The only thing I did was removed the between. Since you are looking for everything past a given 24-hr period, why not just do GREATER THAN 24 hours ago unless you are expecting a ton of entries between the probable second or two from the "NOW()" portion.

Have you tried to actually run the query and see the time performance from it?

select 
      service, 
      time, 
      country, 
      city, 
      country_code,
      issue, 
      latitude, 
      longitude
   from 
      messages
   where 
          service = 'myservice'
      and time > DATE_SUB( NOW() , INTERVAL 24 HOUR )
   order by 
      time desc;
user_741950
Related