Bug #83071 MySQL prefers first declared index over more useful other indexes
Submitted: 21 Sep 2016 8:02 Modified: 21 Sep 2016 8:33
Reporter: Kenny Gryp Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S3 (Non-critical)
Version:5.7.14, 5.6.*, 5.7.15 OS:Any
Assigned to: CPU Architecture:Any
Tags: ICP, Optimizer

[21 Sep 2016 8:02] Kenny Gryp
Description:
- MySQL prefers first declared index over more useful other indexes
- Indexes that can make use of ICP have the same cost as less optimal queries

How to repeat:

CREATE TABLE `title` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `title` text NOT NULL,
  `imdb_index` varchar(12) DEFAULT NULL,
  `kind_id` int(11) NOT NULL,
  `production_year` int(11) DEFAULT NULL,
  `imdb_id` int(11) DEFAULT NULL,
  `phonetic_code` varchar(5) DEFAULT NULL,
  `episode_of_id` int(11) DEFAULT NULL,
  `season_nr` int(11) DEFAULT NULL,
  `episode_nr` int(11) DEFAULT NULL,
  `series_years` varchar(49) DEFAULT NULL,
  `title_crc32` int(10) unsigned DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_title_production_year` (`title`(20),`production_year`),
) ENGINE=InnoDB AUTO_INCREMENT=1543721 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

db1-T9 mysql> explain SELECT * FROM title WHERE season_nr != 6 AND title LIKE 'Best of%';
+----+-------------+-------+------------+-------+---------------------------+---------------------------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type  | possible_keys             | key                       | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+-------+---------------------------+---------------------------+---------+------+------+----------+-------------+
| 1  | SIMPLE      | title | NULL       | range | idx_title_production_year | idx_title_production_year | 62      | NULL | 434  | 90.00    | Using where |
+----+-------------+-------+------------+-------+---------------------------+---------------------------+---------+------+------+----------+-------------+

alter table title add index title (title(20));

db1-T9 mysql> explain SELECT * FROM title WHERE season_nr != 6 AND title LIKE 'Best of%';
+----+-------------+-------+------------+-------+---------------------------------+---------------------------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type  | possible_keys                   | key                       | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+-------+---------------------------------+---------------------------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | title | NULL       | range | idx_title_production_year,title | idx_title_production_year | 62      | NULL |  434 |    90.00 | Using where |
+----+-------------+-------+------------+-------+---------------------------------+---------------------------+---------+------+------+----------+-------------+

*************************** 1. row ***************************
                            QUERY: SELECT * FROM title WHERE season_nr != 6 AND title LIKE 'Best of%'
                            TRACE: {
  "steps": [
    {
      "join_preparation": {
        "select#": 1,
        "steps": [
          {
            "expanded_query": "/* select#1 */ select `title`.`id` AS `id`,`title`.`title` AS `title`,`title`.`imdb_index` AS `imdb_index`,`title`.`kind_id` AS `kind_id`,`title`.`production_year` AS `production_year`,`title`.`imdb_id` AS `imdb_id`,`title`.`phonetic_code` AS `phonetic_code`,`title`.`episode_of_id` AS `episode_of_id`,`title`.`season_nr` AS `season_nr`,`title`.`episode_nr` AS `episode_nr`,`title`.`series_years` AS `series_years`,`title`.`title_crc32` AS `title_crc32` from `title` where ((`title`.`season_nr` <> 6) and (`title`.`title` like 'Best of%'))"
          }
        ]
      }
    },
    {
      "join_optimization": {
        "select#": 1,
        "steps": [
          {
            "condition_processing": {
              "condition": "WHERE",
              "original_condition": "((`title`.`season_nr` <> 6) and (`title`.`title` like 'Best of%'))",
              "steps": [
                {
                  "transformation": "equality_propagation",
                  "resulting_condition": "((`title`.`season_nr` <> 6) and (`title`.`title` like 'Best of%'))"
                },
                {
                  "transformation": "constant_propagation",
                  "resulting_condition": "((`title`.`season_nr` <> 6) and (`title`.`title` like 'Best of%'))"
                },
                {
                  "transformation": "trivial_condition_removal",
                  "resulting_condition": "((`title`.`season_nr` <> 6) and (`title`.`title` like 'Best of%'))"
                }
              ]
            }
          },
          {
            "substitute_generated_columns": {
            }
          },
          {
            "table_dependencies": [
              {
                "table": "`title`",
                "row_may_be_null": false,
                "map_bit": 0,
                "depends_on_map_bits": [
                ]
              }
            ]
          },
          {
            "ref_optimizer_key_uses": [
            ]
          },
          {
            "rows_estimation": [
              {
                "table": "`title`",
                "range_analysis": {
                  "table_scan": {
                    "rows": 1398304,
                    "cost": 285591
                  },
                  "potential_range_indexes": [
                    {
                      "index": "PRIMARY",
                      "usable": false,
                      "cause": "not_applicable"
                    },
                    {
                      "index": "idx_title_production_year",
                      "usable": true,
                      "key_parts": [
                        "title",
                        "production_year",
                        "id"
                      ]
                    },
                    {
                      "index": "title",
                      "usable": true,
                      "key_parts": [
                        "title",
                        "id"
                      ]
                    }
                  ],
                  "setup_range_conditions": [
                  ],
                  "group_index_range": {
                    "chosen": false,
                    "cause": "not_group_by_or_distinct"
                  },
                  "analyzing_range_alternatives": {
                    "range_scan_alternatives": [
                      {
                        "index": "idx_title_production_year",
                        "ranges": [
                          "unprintable_blob_value <= title <= unprintable_blob_value"
                        ],
                        "index_dives_for_eq_ranges": true,
                        "rowid_ordered": false,
                        "using_mrr": false,
                        "index_only": false,
                        "rows": 434,
                        "cost": 521.81,
                        "chosen": true
                      },
                      {
                        "index": "title",
                        "ranges": [
                          "unprintable_blob_value <= title <= unprintable_blob_value"
                        ],
                        "index_dives_for_eq_ranges": true,
                        "rowid_ordered": false,
                        "using_mrr": false,
                        "index_only": false,
                        "rows": 434,
                        "cost": 521.81,
                        "chosen": false,
                        "cause": "cost"
                      }
                    ],
                    "analyzing_roworder_intersect": {
                      "usable": false,
                      "cause": "too_few_roworder_scans"
                    }
                  },
                  "chosen_range_access_summary": {
                    "range_access_plan": {
                      "type": "range_scan",
                      "index": "idx_title_production_year",
                      "rows": 434,
                      "ranges": [
                        "unprintable_blob_value <= title <= unprintable_blob_value"
                      ]
                    },
                    "rows_for_plan": 434,
                    "cost_for_plan": 521.81,
                    "chosen": true
                  }
                }
              }
            ]
          },
            "considered_execution_plans": [
              {
                "plan_prefix": [
                ],
                "table": "`title`",
                "best_access_path": {
           ],
                "table": "`title`",
                "best_access_path": {
                  "considered_access_paths": [
                    {
                      "rows_to_scan": 434,
                      "access_type": "range",
                      "range_details": {
                        "used_index": "idx_title_production_year"
                      },
                      "resulting_rows": 434,
                      "cost": 608.61,
                      "chosen": true
                    }
                  ]
                },
                "condition_filtering_pct": 100,
                "rows_for_plan": 434,
                "cost_for_plan": 608.61,
                "chosen": true
              }
            ]
          },
          {
            "attaching_conditions_to_tables": {
              "original_condition": "((`title`.`season_nr` <> 6) and (`title`.`title` like 'Best of%'))",
              "attached_conditions_computation": [
              ],
              "attached_conditions_summary": [
                {
                  "table": "`title`",
                  "attached": "((`title`.`season_nr` <> 6) and (`title`.`title` like 'Best of%'))"
                }
              ]
            }
          },
          {
            "refine_plan": [
              {
                "table": "`title`"
              }
            ]
          }
        ]
      }
    },
    {
      "join_execution": {
        "select#": 1,
        "steps": [
        ]
      }
    }
  ]
}

I would like to see the index on only title being used as opposed of the composite index as it's smaller.
But that's not the biggest problem...

Now let's try another index:

alter table title add index t_sn (title(20),season_nr);

Now we have a create table of:

CREATE TABLE `title` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `title` text NOT NULL,
  `imdb_index` varchar(12) DEFAULT NULL,
  `kind_id` int(11) NOT NULL,
  `production_year` int(11) DEFAULT NULL,
  `imdb_id` int(11) DEFAULT NULL,
  `phonetic_code` varchar(5) DEFAULT NULL,
  `episode_of_id` int(11) DEFAULT NULL,
  `season_nr` int(11) DEFAULT NULL,
  `episode_nr` int(11) DEFAULT NULL,
  `series_years` varchar(49) DEFAULT NULL,
  `title_crc32` int(10) unsigned DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_title_production_year` (`title`(20),`production_year`),
  KEY `title` (`title`(20)),
  KEY `t_sn` (`title`(20),`season_nr`)
) ENGINE=InnoDB AUTO_INCREMENT=1543721 DEFAULT CHARSET=utf8;

db1-T9 mysql> explain SELECT * FROM title WHERE season_nr != 6 AND title LIKE 'Best of%';
+----+-------------+-------+------------+-------+--------------------------------------+---------------------------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type  | possible_keys                        | key                       | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+-------+--------------------------------------+---------------------------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | title | NULL       | range | idx_title_production_year,title,t_sn | idx_title_production_year | 62      | NULL |  434 |    90.00 | Using where |
+----+-------------+-------+------------+-------+--------------------------------------+---------------------------+---------+------+------+----------+-------------+

Hm.. I would expect t_sn would be used with ICP, but it does not...

{
  "steps": [
    {
      "join_preparation": {
        "select#": 1,
        "steps": [
          {
            "expanded_query": "/* select#1 */ select `title`.`id` AS `id`,`title`.`title` AS `title`,`title`.`imdb_index` AS `imdb_index`,`title`.`kind_id` AS `kind_id`,`title`.`production_year` AS `production_year`,`title`.`imdb_id` AS `imdb_id`,`title`.`phonetic_code` AS `phonetic_code`,`title`.`episode_of_id` AS `episode_of_id`,`title`.`season_nr` AS `season_nr`,`title`.`episode_nr` AS `episode_nr`,`title`.`series_years` AS `series_years`,`title`.`title_crc32` AS `title_crc32` from `title` where ((`title`.`season_nr` <> 6) and (`title`.`title` like 'Best of%'))"
          }
        ]
      }
    },
    {
      "join_optimization": {
        "select#": 1,
        "steps": [
          {
            "condition_processing": {
              "condition": "WHERE",
              "original_condition": "((`title`.`season_nr` <> 6) and (`title`.`title` like 'Best of%'))",
              "steps": [
                {
                  "transformation": "equality_propagation",
                  "resulting_condition": "((`title`.`season_nr` <> 6) and (`title`.`title` like 'Best of%'))"
                },
                {
                  "transformation": "constant_propagation",
                  "resulting_condition": "((`title`.`season_nr` <> 6) and (`title`.`title` like 'Best of%'))"
                },
                {
                  "transformation": "trivial_condition_removal",
                  "resulting_condition": "((`title`.`season_nr` <> 6) and (`title`.`title` like 'Best of%'))"
                }
              ]
            }
          },
          {
            "substitute_generated_columns": {
            }
          },
          {
            "table_dependencies": [
              {
                "table": "`title`",
                "row_may_be_null": false,
                "map_bit": 0,
                "depends_on_map_bits": [
                ]
              }
            ]
          },
          {
            "ref_optimizer_key_uses": [
            ]
          },
          {
            "rows_estimation": [
              {
                "table": "`title`",
                "range_analysis": {
                  "table_scan": {
                    "rows": 1398304,
                    "cost": 285591
                  },
                  "potential_range_indexes": [
                    {
                      "index": "PRIMARY",
                      "usable": false,
                      "cause": "not_applicable"
                    },
                    {
                      "index": "idx_title_production_year",
                      "usable": true,
                      "key_parts": [
                        "title",
                        "production_year",
                        "id"
                      ]
                    },
                    {
                      "index": "title",
                      "usable": true,
                      "key_parts": [
                        "title",
                        "id"
                      ]
                    },
                    {
                      "index": "t_sn",
                      "usable": true,
                      "key_parts": [
                        "title",
                        "season_nr",
                        "id"
                      ]
                    }
                  ],
                  "setup_range_conditions": [
                  ],
                  "group_index_range": {
                    "chosen": false,
                    "cause": "not_group_by_or_distinct"
                  },
                  "analyzing_range_alternatives": {
                    "range_scan_alternatives": [
                      {
                        "index": "idx_title_production_year",
                        "ranges": [
                          "unprintable_blob_value <= title <= unprintable_blob_value"
                        ],
                        "index_dives_for_eq_ranges": true,
                        "rowid_ordered": false,
                        "using_mrr": false,
                        "index_only": false,
                        "rows": 434,
                        "cost": 521.81,
                        "chosen": true
                      },
                      {
                        "index": "title",
                        "ranges": [
                          "unprintable_blob_value <= title <= unprintable_blob_value"
                        ],
                        "index_dives_for_eq_ranges": true,
                        "rowid_ordered": false,
                        "using_mrr": false,
                        "index_only": false,
                        "rows": 434,
                        "cost": 521.81,
                        "chosen": false,
                        "cause": "cost"
                      },
                      {
                        "index": "t_sn",
                        "ranges": [
                          "unprintable_blob_value <= title <= unprintable_blob_value"
                        ],
                        "index_dives_for_eq_ranges": true,
                        "rowid_ordered": false,
                        "using_mrr": false,
                        "index_only": false,
                        "rows": 434,
                        "cost": 521.81,
                        "chosen": false,
                        "cause": "cost"
                      }
                    ],
                    "analyzing_roworder_intersect": {
                      "usable": false,
                      "cause": "too_few_roworder_scans"
                    }
                  },
                  "chosen_range_access_summary": {
                    "range_access_plan": {
                      "type": "range_scan",
                      "index": "idx_title_production_year",
                      "rows": 434,
                      "ranges": [
                        "unprintable_blob_value <= title <= unprintable_blob_value"
                      ]
                    },
                    "rows_for_plan": 434,
                    "cost_for_plan": 521.81,
                    "chosen": true
                  }
                }
              }
            ]
          },
          {
            "considered_execution_plans": [
              {
                "plan_prefix": [
                ],
                "table": "`title`",
                "best_access_path": {
                  "considered_access_paths": [
                    {
                      "rows_to_scan": 434,
                      "access_type": "range",
                      "range_details": {
                        "used_index": "idx_title_production_year"
                      },
                      "resulting_rows": 434,
                      "cost": 608.61,
                      "chosen": true
                    }
                  ]
                },
                "condition_filtering_pct": 100,
                "rows_for_plan": 434,
                "cost_for_plan": 608.61,
                "chosen": true
              }
            ]
          },
          {
            "attaching_conditions_to_tables": {
              "original_condition": "((`title`.`season_nr` <> 6) and (`title`.`title` like 'Best of%'))",
              "attached_conditions_computation": [
              ],
              "attached_conditions_summary": [
                {
                  "table": "`title`",
                  "attached": "((`title`.`season_nr` <> 6) and (`title`.`title` like 'Best of%'))"
                }
              ]
            }
          },
          {
            "refine_plan": [
              {
                "table": "`title`"
              }
            ]
          }
        ]
      }
    },
    {
      "join_execution": {
        "select#": 1,
        "steps": [
        ]
      }
    }
  ]
}

So the cost of all these indexes is the same, while that's not true, the ICP should have lower cost

Handler stats Using the chosen index:

+----------------------------+-------+
| 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          | 434   |
| 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     |
+----------------------------+-------+

Handler stats  while forcing index t_sn:

db1-T9 mysql> explain SELECT * FROM title force index (t_sn) WHERE season_nr != 6 AND title LIKE 'Best of%';
+----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+------------------------------------+
| id | select_type | table | partitions | type  | possible_keys | key  | key_len | ref  | rows | filtered | Extra                              |
+----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+------------------------------------+
|  1 | SIMPLE      | title | NULL       | range | t_sn          | t_sn | 67      | NULL |  434 |    90.00 | Using index condition; Using where |
+----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+------------------------------------+
1 row in set, 1 warning (0.00 sec)

db1-T9 mysql> show status like 'ha%';
+----------------------------+-------+
| 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          | 154   |
| 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     |
+----------------------------+-------+
18 rows in set (0.00 sec)

It's a lot better to use the composite index and use ICP.

Now you can make it work with ICP, if you remove all indexes and put the t_sn composite index as the first index, then that one will be chosen:

db1-T9 mysql> alter table title drop index idx_title_production_year, drop index title, drop index t_sn;
db1-T9 mysql> alter table title  add index `t_sn` (`title`(20),`season_nr`), add index `title` (`title`(20)), add index `idx_title_production_year` (`title`(20),`production_year`);
db1-T9 mysql> show create table title\G

CREATE TABLE `title` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `title` text NOT NULL,
  `imdb_index` varchar(12) DEFAULT NULL,
  `kind_id` int(11) NOT NULL,
  `production_year` int(11) DEFAULT NULL,
  `imdb_id` int(11) DEFAULT NULL,
  `phonetic_code` varchar(5) DEFAULT NULL,
  `episode_of_id` int(11) DEFAULT NULL,
  `season_nr` int(11) DEFAULT NULL,
  `episode_nr` int(11) DEFAULT NULL,
  `series_years` varchar(49) DEFAULT NULL,
  `title_crc32` int(10) unsigned DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `t_sn` (`title`(20),`season_nr`),
  KEY `title` (`title`(20)),
  KEY `idx_title_production_year` (`title`(20),`production_year`)
) ENGINE=InnoDB AUTO_INCREMENT=1543721 DEFAULT CHARSET=utf8

explain SELECT * FROM title WHERE season_nr != 6 AND title LIKE 'Best of%';
+----+-------------+-------+------------+-------+--------------------------------------+------+---------+------+------+----------+------------------------------------+
| id | select_type | table | partitions | type  | possible_keys                        | key  | key_len | ref  | rows | filtered | Extra                              |
+----+-------------+-------+------------+-------+--------------------------------------+------+---------+------+------+----------+------------------------------------+
|  1 | SIMPLE      | title | NULL       | range | t_sn,title,idx_title_production_year | t_sn | 67      | NULL |  434 |    90.00 | Using index condition; Using where |
+----+-------------+-------+------------+-------+--------------------------------------+------+---------+------+------+----------+------------------------------------+

Suggested fix:
- Make the optimizer choose the right index and know that using the index that can use ICP has a lower cost than the other indexes.
- The order of declared indexes should not matter
[21 Sep 2016 8:33] MySQL Verification Team
Hello Kenny,

Thank you for the report and test case.
Observed this with 5.7.15 build.

Thanks,
Umesh