Bug #70245 incorrect costing for range scan causes optimizer to choose incorrect index
Submitted: 4 Sep 2013 21:45 Modified: 5 Sep 2013 16:57
Reporter: trevor price Email Updates:
Status: Not a Bug Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S2 (Serious)
Version:5.6.13 OS:Linux (2.6.16.33-xenU x86_64)
Assigned to: Jørgen Løland
Tags: optimizer cost range_scan

[4 Sep 2013 21:45] trevor price
Description:
The optimizer is producing a very inaccurate index cost, which is causing the wrong index to be selected.  I have encountered this same issue multiple times for range scans.   Its as if the cost is being calculated AS IF it was not a range scan but an equals to.  In this case the range is an in clause, not > or < comparison.

This is a single table query.  Here is the table

Create Table: CREATE TABLE `audio_media_play` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `play_hash` varchar(255) NOT NULL,
  `streaming` enum('1','0') NOT NULL DEFAULT '1',
  `audio_media_id` bigint(20) unsigned DEFAULT NULL COMMENT 'audio_media.id',
  `audio_media_owner_type` enum('track','track_override','user','station_clip','ad') DEFAULT NULL,
  `audio_media_owner_id` bigint(20) unsigned DEFAULT NULL COMMENT 'track.id or jelli_clip.id or user.id, depending on value of audio_media_type',
  `station_id` bigint(20) unsigned NOT NULL,
  `lead_in` bigint(20) unsigned NOT NULL,
  `lead_out` bigint(20) unsigned NOT NULL,
  `start_server_time` datetime DEFAULT NULL,
  `start_device_time` datetime DEFAULT NULL,
  `stop_server_time` datetime DEFAULT NULL COMMENT 'time that the server was notified of play stop',
  `stop_device_time` datetime DEFAULT NULL,
  `percent_played` tinyint(4) DEFAULT NULL,
  `last_modified_server_time` datetime DEFAULT NULL,
  `version` int(10) unsigned NOT NULL DEFAULT '1',
  `start_server_unix_timestamp` bigint(20) unsigned DEFAULT NULL,
  `start_device_unix_timestamp` bigint(20) unsigned DEFAULT NULL,
  `stop_server_unix_timestamp` bigint(20) unsigned DEFAULT NULL,
  `stop_device_unix_timestamp` bigint(20) unsigned DEFAULT NULL,
  `last_modified_timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `play_hash_idx` (`play_hash`),
  KEY `audio_media_id_idx` (`audio_media_id`),
  KEY `streaming_idx` (`streaming`),
  KEY `audio_media_owner_id_idx` (`audio_media_owner_id`),
  KEY `station_id_start_server_time` (`station_id`,`start_server_time`),
  KEY `station_id_start_server_unix_timestamp` (`station_id`,`start_server_unix_timestamp`),
  KEY `last_modified_server_time_idx` (`last_modified_server_time`),
  KEY `last_modified_timestamp_idx` (`last_modified_timestamp`)
) ENGINE=InnoDB ;

here is the query

select 
audio_media_owner_id 
from audio_media_play 
where 
station_id = 3555 
and audio_media_owner_type = 'STATION_CLIP'
and audio_media_owner_id in (48901, 48900, 48903, 48902, 48897, 48893, 48892, 48896, 48895,48899, 48898, 48894) 
and start_server_unix_timestamp  >= 1378227067247 order by start_server_unix_timestamp desc

Now the explain for 5.5.8

*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: audio_media_play
         type: range
possible_keys: audio_media_owner_id_idx,station_id_start_server_time,station_id_start_server_unix_timestamp
          key: station_id_start_server_unix_timestamp
      key_len: 17
          ref: NULL
         rows: 996
        Extra: Using where

And the explain for 5.6.13

*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: audio_media_play
         type: range
possible_keys: audio_media_owner_id_idx,station_id_start_server_time,station_id_start_server_unix_timestamp
          key: audio_media_owner_id_idx
      key_len: 9
          ref: NULL
         rows: 264
        Extra: Using index condition; Using where; Using filesort

Looking at the query trace in 5.6.13 it appears the cost for audio_media_owner_id_idx is too small.  An analyze table was performed on the table.

How to repeat:
Create Table: CREATE TABLE `audio_media_play` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `play_hash` varchar(255) NOT NULL,
  `streaming` enum('1','0') NOT NULL DEFAULT '1',
  `audio_media_id` bigint(20) unsigned DEFAULT NULL COMMENT 'audio_media.id',
  `audio_media_owner_type` enum('track','track_override','user','station_clip','ad') DEFAULT NULL,
  `audio_media_owner_id` bigint(20) unsigned DEFAULT NULL COMMENT 'track.id or jelli_clip.id or user.id, depending on value of audio_media_type',
  `station_id` bigint(20) unsigned NOT NULL,
  `lead_in` bigint(20) unsigned NOT NULL,
  `lead_out` bigint(20) unsigned NOT NULL,
  `start_server_time` datetime DEFAULT NULL,
  `start_device_time` datetime DEFAULT NULL,
  `stop_server_time` datetime DEFAULT NULL COMMENT 'time that the server was notified of play stop',
  `stop_device_time` datetime DEFAULT NULL,
  `percent_played` tinyint(4) DEFAULT NULL,
  `last_modified_server_time` datetime DEFAULT NULL,
  `version` int(10) unsigned NOT NULL DEFAULT '1',
  `start_server_unix_timestamp` bigint(20) unsigned DEFAULT NULL,
  `start_device_unix_timestamp` bigint(20) unsigned DEFAULT NULL,
  `stop_server_unix_timestamp` bigint(20) unsigned DEFAULT NULL,
  `stop_device_unix_timestamp` bigint(20) unsigned DEFAULT NULL,
  `last_modified_timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `play_hash_idx` (`play_hash`),
  KEY `audio_media_id_idx` (`audio_media_id`),
  KEY `streaming_idx` (`streaming`),
  KEY `audio_media_owner_id_idx` (`audio_media_owner_id`),
  KEY `station_id_start_server_time` (`station_id`,`start_server_time`),
  KEY `station_id_start_server_unix_timestamp` (`station_id`,`start_server_unix_timestamp`),
  KEY `last_modified_server_time_idx` (`last_modified_server_time`),
  KEY `last_modified_timestamp_idx` (`last_modified_timestamp`)
) ENGINE=InnoDB ;

here is the query

select 
audio_media_owner_id 
from audio_media_play 
where 
station_id = 3555 
and audio_media_owner_type = 'STATION_CLIP'
and audio_media_owner_id in (48901, 48900, 48903, 48902, 48897, 48893, 48892, 48896, 48895,48899, 48898, 48894) 
and start_server_unix_timestamp  >= 1378227067247 order by start_server_unix_timestamp desc
[4 Sep 2013 21:48] trevor price
Query trace on 5.6.13

Attachment: querytrace.rtf (text/rtf), 15.75 KiB.

[4 Sep 2013 22:12] trevor price
a zip file has been loaded that contains data allowing replication of the issue.  the data file is bug-data-70245.zip
[4 Sep 2013 22:24] trevor price
further demonstrate the wrong index is selected.

Server version: 5.6.13-log MySQL Community Server (GPL)

Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> flush status ;
Query OK, 0 rows affected (0.00 sec)

mysql> select audio_media_owner_id 
    -> from audio_media_play  
    -> where station_id = 3555 and audio_media_owner_type = 'STATION_CLIP'
    -> and audio_media_owner_id in (48901, 48900, 48903, 48902, 48897, 48893, 48892, 48896, 48895, 48899, 48898, 48894) 
    -> and start_server_unix_timestamp  >= 1378227067247 order by start_server_unix_timestamp desc
    -> \G

----snipped------

mysql> show session status like 'hand%' ;
+----------------------------+-------+
| Variable_name              | Value |
+----------------------------+-------+
| Handler_commit             | 1     |
| Handler_delete             | 0     |
| Handler_discover           | 0     |
| Handler_external_lock      | 2     |
| Handler_mrr_init           | 0     |
| Handler_prepare            | 0     |
| Handler_read_first         | 0     |
| Handler_read_key           | 12    |
| Handler_read_last          | 0     |
| Handler_read_next          | 11088 |
| Handler_read_prev          | 0     |
| Handler_read_rnd           | 0     |
| Handler_read_rnd_next      | 0     |
| Handler_rollback           | 0     |
| Handler_savepoint          | 0     |
| Handler_savepoint_rollback | 0     |
| Handler_update             | 0     |
| Handler_write              | 0     |
+----------------------------+-------+

now , forcing 5.6.13 to use the correct index

mysql> flush status ;
Query OK, 0 rows affected (0.00 sec)

select audio_media_owner_id from audio_media_play use index ( station_id_start_server_unix_timestamp) where station_id = 3555 and audio_media_owner_type = 'STATION_CLIP' and audio_media_owner_id in (48901, 48900, 48903, 48902, 48897, 48893, 48892, 48896, 48895, 48899, 48898, 48894) and start_server_unix_timestamp  >= 1378227067247 order by start_server_unix_timestamp desc ;

----snipped----

mysql> show session status like 'hand%' ;
+----------------------------+-------+
| Variable_name              | Value |
+----------------------------+-------+
| Handler_commit             | 1     |
| Handler_delete             | 0     |
| Handler_discover           | 0     |
| Handler_external_lock      | 2     |
| Handler_mrr_init           | 0     |
| Handler_prepare            | 0     |
| Handler_read_first         | 0     |
| Handler_read_key           | 1     |
| Handler_read_last          | 0     |
| Handler_read_next          | 0     |
| Handler_read_prev          | 1005  |
| Handler_read_rnd           | 0     |
| Handler_read_rnd_next      | 0     |
| Handler_rollback           | 0     |
| Handler_savepoint          | 0     |
| Handler_savepoint_rollback | 0     |
| Handler_update             | 0     |
| Handler_write              | 0     |
+----------------------------+-------+

so that's 1,000 read_prev for the correct index and 11,000 read_next for the index chosen by 5.6.13
[5 Sep 2013 8:58] Jørgen Løland
Hi Trevor,

MySQL 5.6 has a new variable called eq_range_index_dive_limit. It determines whether or not to do 

a. expensive but accurate statistics gathering, or
b. cheap but less accurate statistics gathering

for conditions that can be translated to equality predicates (e.g. IN). The value is the maximum number of ranges there can be before we switch from a. to b.  The default value is 10, and in your query you have 12 ranges, which in turn means that method b. is chosen.

If you set eq_range_index_dive_limit to something greater than 12 or 0 (a. is always used) you should get the <5.6 behavior back.

I'll close the bug - please reopen it if the suggestion above does not work for you.
[5 Sep 2013 16:57] trevor price
yes, changing the variable specified changes the optimizer plan.  Thanks.