Geek Answers Handbook

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

I tested various ideas to optimize some of the tables that we have in our system at work. Today I came across a table that tracks every kind of every car in our system. Create the 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 played with multi-column and single-column indices. I performed 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 |                                              |
+----+-------------+-----------------------+--------+----------------------------------------------------------------+------------------------------------+---------+----------------------------------------------+-------+----------+----------------------------------------------+

(The lead time for the current selection request was 0.309 seconds)

then I change the date in the where clause from '2011-09-07' to '2011-07-07' and get the following explanation 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 |                                              |
+----+-------------+-----------------------+--------+----------------------------------------------------------------+-----------------------------+---------+----------------------------------------------+-------+----------+----------------------------------------------+

(The lead time for the current selection request was 0.670 seconds)

I see 4 major changes:

  • changed from range to ref Key
  • vehicleViewTrackingKeyCreatedIndex vehicleViewTrackingKeyIndex
  • key_len 50 47 ( )
  • 23086 53676 ( )

0,6 , 10% .

, - mysql, , (, , ) , where.

. -, / , , , . , : -)

+5
1

. , (, ) , . - , .

: : id ( ), (), . , . MySQL , : -, ( , log (n), n - ), . , .

name like 'Z%'. , , . , Zs , . ( ).

, , M-Z. . , , : , , .

, - , , . ( , , , , . , MySQL .)

, , . , , , . , .

(public_key, created_on_date) , . , MySQL ( ). , , .

[ InnoDB , . .]

+7

More articles:


All Articles