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