MySQL EXPLAIN 'type' changes from 'range' to 'ref' when the date in the where statement is changed?

Question

I've been testing out different ideas for optimizing some of the tables we have in our system at work. Today I came across a table that tracks every view on each vehicle in our system. Create table below.

SHOW CREATE TABLE vehicle_view_tracking;

CREATE TABLE `vehicle_view_tracking` (
  `vehicle_view_tracking_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `public_key` varchar(45) NOT NULL,
  `vehicle_id` int(10) unsigned NOT NULL,
  `landing_url` longtext NOT NULL,
  `landing_port` int(11) NOT NULL,
  `http_referrer` longtext,
  `created_on` datetime NOT NULL,
  `created_on_date` date NOT NULL,
  `server_host` longtext,
  `server_uri` longtext,
  `referrer_host` longtext,
  `referrer_uri` longtext,
  PRIMARY KEY (`vehicle_view_tracking_id`),
  KEY `vehicleViewTrackingKeyCreatedIndex` (`public_key`,`created_on_date`),
  KEY `vehicleViewTrackingKeyIndex` (`public_key`)
) ENGINE=InnoDB AUTO_INCREMENT=363439 DEFAULT CHARSET=latin1;

I was playing around with multi-column and single column indexes. I ran the following query:

EXPLAIN EXTENDED SELECT dealership_vehicles.vehicle_make, dealership_vehicles.vehicle_model, vehicle_view_tracking.referrer_host, count(*) AS count
FROM vehicle_view_tracking
LEFT JOIN dealership_vehicles
ON dealership_vehicles.dealership_vehicle_id = vehicle_view_tracking.vehicle_id
WHERE vehicle_view_tracking.created_on_date >= '2011-09-07' AND vehicle_view_tracking.public_key IN ('ab12c3')
GROUP BY (dealership_vehicles.vehicle_make) ASC , dealership_vehicles.vehicle_model, referrer_host

+----+-------------+-----------------------+--------+----------------------------------------------------------------+------------------------------------+---------+----------------------------------------------+-------+----------+----------------------------------------------+
| id | select_type | table                 | type   | possible_keys                                                  | key                                | key_len | ref                                          | rows  | filtered | Extra                                        |
+----+-------------+-----------------------+--------+----------------------------------------------------------------+------------------------------------+---------+----------------------------------------------+-------+----------+----------------------------------------------+
|  1 | SIMPLE      | vehicle_view_tracking | range  | vehicleViewTrackingKeyCreatedIndex,vehicleViewTrackingKeyIndex | vehicleViewTrackingKeyCreatedIndex | 50      | NULL                                         | 23086 |   100.00 | Using where; Using temporary; Using filesort |
|  1 | SIMPLE      | dealership_vehicles   | eq_ref | PRIMARY                                                        | PRIMARY                            | 8       | vehicle_view_tracking.vehicle_id |     1 |   100.00 |                                              |
+----+-------------+-----------------------+--------+----------------------------------------------------------------+------------------------------------+---------+----------------------------------------------+-------+----------+----------------------------------------------+

(Execution time for actual select query was .309 seconds)

then I change the date in the where clause from '2011-09-07' to '2011-07-07' and got the following explain results

EXPLAIN EXTENDED SELECT dealership_vehicles.vehicle_make, dealership_vehicles.vehicle_model, vehicle_view_tracking.referrer_host, count(*) AS count
FROM vehicle_view_tracking
LEFT JOIN dealership_vehicles
ON dealership_vehicles.dealership_vehicle_id = vehicle_view_tracking.vehicle_id
WHERE vehicle_view_tracking.created_on_date >= '2011-07-07' AND vehicle_view_tracking.public_key IN ('ab12c3')
GROUP BY (dealership_vehicles.vehicle_make) ASC , dealership_vehicles.vehicle_model, referrer_host


+----+-------------+-----------------------+--------+----------------------------------------------------------------+-----------------------------+---------+----------------------------------------------+-------+----------+----------------------------------------------+
| id | select_type | table                 | type   | possible_keys                                                  | key                         | key_len | ref                                          | rows  | filtered | Extra                                        |
+----+-------------+-----------------------+--------+----------------------------------------------------------------+-----------------------------+---------+----------------------------------------------+-------+----------+----------------------------------------------+
|  1 | SIMPLE      | vehicle_view_tracking | ref    | vehicleViewTrackingKeyCreatedIndex,vehicleViewTrackingKeyIndex | vehicleViewTrackingKeyIndex | 47      | const                                        | 53676 |   100.00 | Using where; Using temporary; Using filesort |
|  1 | SIMPLE      | dealership_vehicles   | eq_ref | PRIMARY                                                        | PRIMARY                     | 8       | vehicle_view_tracking.vehicle_id |     1 |   100.00 |                                              |
+----+-------------+-----------------------+--------+----------------------------------------------------------------+-----------------------------+---------+----------------------------------------------+-------+----------+----------------------------------------------+

(Execution time for actual select query was .670 seconds)

I see 4 main changes:

1. type changed from range to ref
2. key changed from vehicleViewTrackingKeyCreatedIndex to vehicleViewTrackingKeyIndex
3. key_len changed from 50 to 47 (caused by the change in key)
4. rows changed from 23086 to 53676 (caused by the change in key)

At this point, the execution time is only .6 seconds for the slow query however we only have about 10% of our vehicles in our database.

It's getting late and I may have overlooked something in the mysql docs but I can't seem to find why the key (and in turn the type and rows) are changing when the date is changed in the where clause.

The help is greatly appreciated. I searched for someone having the same/similar issue with a date causing this change and was not able to find anything. If I missed a previous post, please link me :-)

Answer 1

Different search strategies make sense for different data. In particular, index scans (such as range) often have to do a seek to actually read the row. At some point, doing all those seeks is slower than not using the index at all.

Take a trivial example, a table with three columns: id (primary key), name (indexed), birthday. Say it has a lot of data. If you ask MySQL to look for Bob's birthday, it can do that fairly quickly: first, it finds Bob in the name index (this takes a few seeks, log(n) where n is the row count), then one additional seek to read the actual row in the data file and read the birthday from it. That's very quick, and far quicker than scanning the entire table.

Next, consider doing a name like 'Z%'. That is probably a fairly small portion of the table. So its still faster to find where the Zs start in the name index, then for each one seek the data file to read the row. (This is a range scan).

Finally, consider asking for all names starting with M-Z. That's probably around half the data. It could do a range scan, and then a lot of seeks, but seeking randomly over the datafile with the ultimate goal of reading half the rows isn't optimal: it'd be faster to just do a big sequential read over the data file. So, in this case, the index will be ignored.

This is what you're seeing—except in your case, there is another key it can fall back on. (Its also possible that it might actually use the date index if it didn't have the other, it should pick whichever index will be quickest. Beware that MySQL's optimizer often makes errors in this.)

So, in short, this is expected. A query doesn't say how to retrieve the data, rather it says what data to retrieve. The database's optimizer is supposed to find the quickest way to retrieve it.

You may find an index on both columns, in the order (public_key,created_on_date) is preferred in both cases, and speeds up your query. This is because MySQL can only ever use one index per table (per query). Also, the date goes at the end because a range scan can only be done efficiently on the last column in an index.

[InnoDB actually has another layer of indirection, I believe, but it'd just confuse the point. It doesn't make a difference to the explanation.]