### Author:  [@Jamie\_Downs](https://forums.percona.com/u/Jamie_Downs)
#### Post date: [November 29, 2021, 1:41pm UTC](https://forums.percona.com/t/wrong-index-chosen/13263/1 "2021-11-29T13:41:15Z")
Hello,
I would truely appreciate any pointers on this as I’m out of ideas.
I have the following query, schema and plan.
There are about 18 million records in the table and last week the optimizer chose to use the delete\_old key rather than taskid or taskid2. The consequence was the query taking 100 minutes to complete.
If I force one of the other indexes the query returns instantly.
# Query
select id as id1\_123\_,
comment as comment2\_123\_,
created\_by as created\_3\_123\_,
created\_date as created\_4\_123\_,
document\_id as document5\_123\_,
instance\_id as instance6\_123\_,
major\_version as major\_ve7\_123\_,
message as message8\_123\_,
minor\_version as minor\_ve9\_123\_,
notification as notific10\_123\_,
notification\_type as notific11\_123\_,
object\_key as object\_12\_123\_,
object\_record\_id as object\_13\_123\_,
recipient\_id as recipie14\_123\_,
sender\_id as sender\_15\_123\_,
status as status16\_123\_,
subject as subject17\_123\_,
task\_id as task\_id18\_123\_,
template\_id as templat19\_123\_,
version as version20\_123\_,
workflow\_id as workflo21\_123\_,
workflow\_step\_id as workflo22\_123\_
```
from notification
where document_id = 2924508
and task_id = '17952762'
and instance_id = 99
order by created_date ASC limit 1
```
# Table
CREATE TABLE notification (
id bigint(20) NOT NULL AUTO\_INCREMENT,
created\_by bigint(20) NOT NULL,
created\_date datetime NOT NULL,
document\_id bigint(20) DEFAULT NULL,
instance\_id bigint(20) NOT NULL,
message longtext CHARACTER SET utf8 COLLATE utf8\_bin NOT NULL,
notification varchar(4000) DEFAULT NULL,
recipient\_id bigint(20) NOT NULL,
sender\_id bigint(20) NOT NULL,
status varchar(20) CHARACTER SET utf8 COLLATE utf8\_bin DEFAULT NULL,
subject varchar(255) CHARACTER SET utf8 COLLATE utf8\_bin DEFAULT NULL,
task\_id varchar(40) CHARACTER SET utf8 COLLATE utf8\_bin DEFAULT NULL,
template\_id bigint(20) NOT NULL,
version int(11) DEFAULT NULL,
workflow\_id varchar(40) CHARACTER SET utf8 COLLATE utf8\_bin DEFAULT NULL,
workflow\_step\_id varchar(255) DEFAULT NULL,
object\_key varchar(100) DEFAULT NULL,
object\_record\_id varchar(100) DEFAULT NULL,
major\_version int(11) DEFAULT NULL,
minor\_version int(11) DEFAULT NULL,
comment varchar(5000) DEFAULT NULL,
notification\_type varchar(30) DEFAULT NULL,
PRIMARY KEY (id),
KEY workflow\_id (workflow\_id),
KEY delete\_old (created\_date),
KEY recipient\_id (recipient\_id,status,notification\_type,created\_date),
KEY task\_id (document\_id,task\_id,created\_date),
KEY task\_id2 (task\_id),
KEY object\_key (object\_key,object\_record\_id)
) ENGINE=InnoDB AUTO\_INCREMENT=25834129 DEFAULT CHARSET=utf8
# Explain
±—±------------±-------------±-----------±------±-----------------±-----------±--------±-----±-----±---------±------------+
| id | select\_type | table | partitions | type | possible\_keys | key | key\_len | ref | rows | filtered | Extra |
±—±------------±-------------±-----------±------±-----------------±-----------±--------±-----±-----±---------±------------+
| 1 | SIMPLE | notification | NULL | index | task\_id,task\_id2 | delete\_old | 5 | NULL | 2516 | 0.01 | Using where |
±—±------------±-------------±-----------±------±-----------------±-----------±--------±-----±-----±---------±------------+
I thought it might be a statistics thing so tried updating them as post showed to no avail. ([https://www.percona.com/blog/2017/09/11/updating-innodb-table-statistics-manually/](https://www.percona.com/blog/2017/09/11/updating-innodb-table-statistics-manually/)).
Please can anyone suggest anything I might have missed?
---
### Author:  [@CTutte](https://forums.percona.com/u/CTutte)
#### Post date: [November 29, 2021, 1:55pm UTC](https://forums.percona.com/t/wrong-index-chosen/13263/2 "2021-11-29T13:55:08Z")
Hi 8adger,
Did you try increasing innodb\_stats\_sample\_pages ?
if there is high fragmentation or if the row size varies too much between different disk pages, the sampling might collect misleading information and then the optimizer will have misleading information to execute the query.
If the table is highly fragmented and is not huge, you can try rebuilding it with “ALTER TABLE \ engine=innodb” .
You can also try to increase the number of sampled pages so that more disk pages are sampled and a more accurate sampling is done. By default only 20 pages are sampled (320 kb) so for a large table with a high variance in row size, the collected information might vary from run to run.
Last, if you already know that this query execution MUST use a certain index (and if the table gets frequently modified/purged) you should leave the index hint to avoid the optimizer from picking the wrong index again…
Regards
---
### Author:  [@Jamie\_Downs](https://forums.percona.com/u/Jamie_Downs)
#### Post date: [November 29, 2021, 2:02pm UTC](https://forums.percona.com/t/wrong-index-chosen/13263/3 "2021-11-29T14:02:44Z")
Many thanks for your quick response. Yes I tried increasing the innodb\_stats\_sample\_pages until it was a similar number to the actual pages. Still no joy.
The actual issue was on the 22nd.
I have been testing on a replica of the data.
Funnily enough the optimizer is now choosing the correct index on the live server. I have copied all the statistics for the particular table from innodb\_index\_stats table to my test server and did FLUSH table. It is still choosing the wrong index.
Any other ideas please?
---
### Author:  [@CTutte](https://forums.percona.com/u/CTutte)
#### Post date: [November 29, 2021, 2:14pm UTC](https://forums.percona.com/t/wrong-index-chosen/13263/4 "2021-11-29T14:14:36Z")
If after copying the statistics to the test server that causes the wrong index pick, then it’s an statistics issue.
I would say you defragment/rebuild the table the next time it happens, or force the index usage. if the optimizer thinks that he will need to read more than ~30% of the pages it will prefer to do a full table scan instead, although in reality it can be less than 30% of the pages (or it might be faster to go with 30% of the pages if they are on memory).
Regards
---
### Author:  [@Jamie\_Downs](https://forums.percona.com/u/Jamie_Downs)
#### Post date: [November 29, 2021, 2:18pm UTC](https://forums.percona.com/t/wrong-index-chosen/13263/5 "2021-11-29T14:18:35Z")
Hi Carlos, I should have been clearer.
I copied the statistics from the good server to the bad server and that didn’t help.
---
### Author:  [@matthewb](https://forums.percona.com/u/matthewb)
#### Post date: [December 1, 2021, 5:56pm UTC](https://forums.percona.com/t/wrong-index-chosen/13263/6 "2021-12-01T17:56:19Z")
Can you enable optimizer trace and re-run the explain with FORMAT=JSON so we can see all the _reasons_ why the optimizer is picking the wrong index?
---
### Author:  [@Jamie\_Downs](https://forums.percona.com/u/Jamie_Downs)
#### Post date: [February 10, 2022, 7:59pm UTC](https://forums.percona.com/t/wrong-index-chosen/13263/7 "2022-02-10T19:59:20Z")
> [@matthewb](#):
>
> ptimiz
Apologies Matthew. I did not see you comment back in December. I will collect the trace information and share it.
---
### Author:  [@Jamie\_Downs](https://forums.percona.com/u/Jamie_Downs)
#### Post date: [February 10, 2022, 8:24pm UTC](https://forums.percona.com/t/wrong-index-chosen/13263/8 "2022-02-10T20:24:37Z")
Hello Matthew please find optimizer info below. This is for the slow version.
```auto
+----+-------------+--------------+------------+-------+------------------+------------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------------+------------+-------+------------------+------------+---------+------+------+----------+-------------+
| 1 | SIMPLE | bobnotif0_ | NULL | index | task_id,task_id2 | delete_old | 5 | NULL | 3465 | 0.00 | Using where |
+----+-------------+--------------+------------+-------+------------------+------------+---------+------+------+----------+-------------+
Query:
select bobnotif0_.id as id1_123_,
bobnotif0_.comment as comment2_123_,
bobnotif0_.created_by as created_3_123_,
bobnotif0_.created_date as created_4_123_,
bobnotif0_.document_id as document5_123_,
bobnotif0_.instance_id as instance6_123_,
bobnotif0_.major_version as major_ve7_123_,
bobnotif0_.message as message8_123_,
bobnotif0_.minor_version as minor_ve9_123_,
bobnotif0_.notification as notific10_123_,
bobnotif0_.notification_type as notific11_123_,
bobnotif0_.object_key as object_12_123_,
bobnotif0_.object_record_id as object_13_123_,
bobnotif0_.recipient_id as recipie14_123_,
bobnotif0_.sender_id as sender_15_123_,
bobnotif0_.status as status16_123_,
bobnotif0_.subject as subject17_123_,
bobnotif0_.task_id as task_id18_123_,
bobnotif0_.template_id as templat19_123_,
bobnotif0_.version as version20_123_,
bobnotif0_.workflow_id as workflo21_123_,
bobnotif0_.workflow_step_id as workflo22_123_
from bob_notification bobnotif0_
where bobnotif0_.instance_id=5429
and bobnotif0_.task_id='17952762'
and bobnotif0_.document_id=2924508
order by bobnotif0_.created_date ASC limit 1;
SELECT * FROM information_schema.OPTIMIZER_TRACE\G
***************************1. row***************************
QUERY: select bobnotif0_.id as id1_123_,
bobnotif0_.comment as comment2_123_,
bobnotif0_.created_by as created_3_123_,
bobnotif0_.created_date as created_4_123_,
bobnotif0_.document_id as document5_123_,
bobnotif0_.instance_id as instance6_123_,
bobnotif0_.major_version as major_ve7_123_,
bobnotif0_.message as message8_123_,
bobnotif0_.minor_version as minor_ve9_123_,
bobnotif0_.notification as notific10_123_,
bobnotif0_.notification_type as notific11_123_,
bobnotif0_.object_key as object_12_123_,
bobnotif0_.object_record_id as object_13_123_,
bobnotif0_.recipient_id as recipie14_123_,
bobnotif0_.sender_id as sender_15_123_,
bobnotif0_.status as status16_123_,
bobnotif0_.subject as subject17_123_,
bobnotif0_.task_id as task_id18_123_,
bobnotif0_.template_id as templat19_123_,
bobnotif0_.version as version20_123_,
bobnotif0_.workflow_id as workflo21_123_,
bobnotif0_.workflow_step_id as workflo22_123_
from bob_notification bobnotif0_
where bobnotif0_.instance_id=5429
and bobnotif0_.task_id='17952762'
and bobnotif0_.document_id=2924508
order by bobnotif0_.created_date ASC limit 1
TRACE: {
"steps": [
{
"join_preparation": {
"select#": 1,
"steps": [
{
"expanded_query": "/* select#1 */ select `bobnotif0_`.`id` AS `id1_123_`,`bobnotif0_`.`comment` AS `comment2_123_`,`bobnotif0_`.`created_by` AS `created_3_123_`,`bobnotif0_`.`created_date` AS `created_4_123_`,`bobnotif0_`.`document_id` AS `document5_123_`,`bobnotif0_`.`instance_id` AS `instance6_123_`,`bobnotif0_`.`major_version` AS `major_ve7_123_`,`bobnotif0_`.`message` AS `message8_123_`,`bobnotif0_`.`minor_version` AS `minor_ve9_123_`,`bobnotif0_`.`notification` AS `notific10_123_`,`bobnotif0_`.`notification_type` AS `notific11_123_`,`bobnotif0_`.`object_key` AS `object_12_123_`,`bobnotif0_`.`object_record_id` AS `object_13_123_`,`bobnotif0_`.`recipient_id` AS `recipie14_123_`,`bobnotif0_`.`sender_id` AS `sender_15_123_`,`bobnotif0_`.`status` AS `status16_123_`,`bobnotif0_`.`subject` AS `subject17_123_`,`bobnotif0_`.`task_id` AS `task_id18_123_`,`bobnotif0_`.`template_id` AS `templat19_123_`,`bobnotif0_`.`version` AS `version20_123_`,`bobnotif0_`.`workflow_id` AS `workflo21_123_`,`bobnotif0_`.`workflow_step_id` AS `workflo22_123_` from `bob_notification` `bobnotif0_` where ((`bobnotif0_`.`instance_id` = 5429) and (`bobnotif0_`.`task_id` = '17952762') and (`bobnotif0_`.`document_id` = 2924508)) order by `bobnotif0_`.`created_date` limit 1"
}
]
}
},
{
"join_optimization": {
"select#": 1,
"steps": [
{
"condition_processing": {
"condition": "WHERE",
"original_condition": "((`bobnotif0_`.`instance_id` = 5429) and (`bobnotif0_`.`task_id` = '17952762') and (`bobnotif0_`.`document_id` = 2924508))",
"steps": [
{
"transformation": "equality_propagation",
"resulting_condition": "(multiple equal(5429, `bobnotif0_`.`instance_id`) and multiple equal('17952762', `bobnotif0_`.`task_id`) and multiple equal(2924508, `bobnotif0_`.`document_id`))"
},
{
"transformation": "constant_propagation",
"resulting_condition": "(multiple equal(5429, `bobnotif0_`.`instance_id`) and multiple equal('17952762', `bobnotif0_`.`task_id`) and multiple equal(2924508, `bobnotif0_`.`document_id`))"
},
{
"transformation": "trivial_condition_removal",
"resulting_condition": "(multiple equal(5429, `bobnotif0_`.`instance_id`) and multiple equal('17952762', `bobnotif0_`.`task_id`) and multiple equal(2924508, `bobnotif0_`.`document_id`))"
}
]
}
},
{
"substitute_generated_columns": {
}
},
{
"table_dependencies": [
{
"table": "`bob_notification` `bobnotif0_`",
"row_may_be_null": false,
"map_bit": 0,
"depends_on_map_bits": [
]
}
]
},
{
"ref_optimizer_key_uses": [
{
"table": "`bob_notification` `bobnotif0_`",
"field": "document_id",
"equals": "2924508",
"null_rejecting": false
},
{
"table": "`bob_notification` `bobnotif0_`",
"field": "task_id",
"equals": "'17952762'",
"null_rejecting": false
},
{
"table": "`bob_notification` `bobnotif0_`",
"field": "task_id",
"equals": "'17952762'",
"null_rejecting": false
}
]
},
{
"rows_estimation": [
{
"table": "`bob_notification` `bobnotif0_`",
"range_analysis": {
"table_scan": {
"rows": 24905118,
"cost": 3.47e7
},
"potential_range_indexes": [
{
"index": "PRIMARY",
"usable": false,
"cause": "not_applicable"
},
{
"index": "workflow_id",
"usable": false,
"cause": "not_applicable"
},
{
"index": "delete_old",
"usable": false,
"cause": "not_applicable"
},
{
"index": "recipient_id",
"usable": false,
"cause": "not_applicable"
},
{
"index": "task_id",
"usable": true,
"key_parts": [
"document_id",
"task_id",
"created_date",
"id"
]
},
{
"index": "task_id2",
"usable": true,
"key_parts": [
"task_id",
"id"
]
},
{
"index": "object_key",
"usable": false,
"cause": "not_applicable"
}
],
"setup_range_conditions": [
],
"group_index_range": {
"chosen": false,
"cause": "not_group_by_or_distinct"
},
"analyzing_range_alternatives": {
"range_scan_alternatives": [
{
"index": "task_id",
"ranges": [
"2924508 <= document_id <= 2924508 AND 0x0800313739353237363200000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000 <= task_id <= 0x0800313739353237363200000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000"
],
"index_dives_for_eq_ranges": true,
"rowid_ordered": false,
"using_mrr": false,
"index_only": false,
"rows": 9222,
"cost": 11067,
"chosen": true
},
{
"index": "task_id2",
"ranges": [
"0x0800313739353237363200000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000 <= task_id <= 0x0800313739353237363200000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000"
],
"index_dives_for_eq_ranges": true,
"rowid_ordered": true,
"using_mrr": false,
"index_only": false,
"rows": 7188,
"cost": 8626.6,
"chosen": true
}
],
"analyzing_roworder_intersect": {
"usable": false,
"cause": "too_few_roworder_scans"
}
},
"chosen_range_access_summary": {
"range_access_plan": {
"type": "range_scan",
"index": "task_id2",
"rows": 7188,
"ranges": [
"0x0800313739353237363200000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000 <= task_id <= 0x0800313739353237363200000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000"
]
},
"rows_for_plan": 7188,
"cost_for_plan": 8626.6,
"chosen": true
}
}
}
]
},
{
"considered_execution_plans": [
{
"plan_prefix": [
],
"table": "`bob_notification` `bobnotif0_`",
"best_access_path": {
"considered_access_paths": [
{
"access_type": "ref",
"index": "task_id",
"rows": 9222,
"cost": 11066,
"chosen": true
},
{
"access_type": "ref",
"index": "task_id2",
"rows": 7188,
"cost": 8625.6,
"chosen": true
},
{
"access_type": "range",
"range_details": {
"used_index": "task_id2"
},
"chosen": false,
"cause": "heuristic_index_cheaper"
}
]
},
"condition_filtering_pct": 7e-4,
"rows_for_plan": 0.05,
"cost_for_plan": 8625.6,
"chosen": true
}
]
},
{
"attaching_conditions_to_tables": {
"original_condition": "((`bobnotif0_`.`document_id` = 2924508) and (`bobnotif0_`.`task_id` = '17952762') and (`bobnotif0_`.`instance_id` = 5429))",
"attached_conditions_computation": [
],
"attached_conditions_summary": [
{
"table": "`bob_notification` `bobnotif0_`",
"attached": "((`bobnotif0_`.`document_id` = 2924508) and (`bobnotif0_`.`instance_id` = 5429))"
}
]
}
},
{
"clause_processing": {
"clause": "ORDER BY",
"original_clause": "`bobnotif0_`.`created_date`",
"items": [
{
"item": "`bobnotif0_`.`created_date`"
}
],
"resulting_clause_is_simple": true,
"resulting_clause": "`bobnotif0_`.`created_date`"
}
},
{
"added_back_ref_condition": "((`bobnotif0_`.`task_id` <=> '17952762') and ((`bobnotif0_`.`document_id` = 2924508) and (`bobnotif0_`.`instance_id` = 5429)))"
},
{
"reconsidering_access_paths_for_index_ordering": {
"clause": "ORDER BY",
"steps": [
],
"index_order_summary": {
"table": "`bob_notification` `bobnotif0_`",
"index_provides_order": true,
"order_direction": "asc",
"index": "delete_old",
"plan_changed": true,
"access_type": "index"
}
}
},
{
"refine_plan": [
{
"table": "`bob_notification` `bobnotif0_`"
}
]
}
]
}
},
{
"join_execution": {
"select#": 1,
"steps": [
]
}
}
]
}
MISSING_BYTES_BEYOND_MAX_MEM_SIZE: 0
INSUFFICIENT_PRIVILEGES: 0
```
---
### Author:  [@Jamie\_Downs](https://forums.percona.com/u/Jamie_Downs)
#### Post date: [February 10, 2022, 8:37pm UTC](https://forums.percona.com/t/wrong-index-chosen/13263/9 "2022-02-10T20:37:18Z")
For completeness. this is the fast version.
```auto
+----+-------------+--------------+------------+------+------------------+----------+---------+-------+------+----------+----------------------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------------+------------+------+------------------+----------+---------+-------+------+----------+----------------------------------------------------+
| 1 | SIMPLE | bobnotif0_ | NULL | ref | task_id,task_id2 | task_id2 | 123 | const | 4701 | 0.00 | Using index condition; Using where; Using filesort |
+----+-------------+--------------+------------+------+------------------+----------+---------+-------+------+----------+----------------------------------------------------+
SELECT * FROM information_schema.OPTIMIZER_TRACE\G
***************************1. row***************************
QUERY: select bobnotif0_.id as id1_123_,
bobnotif0_.comment as comment2_123_,
bobnotif0_.created_by as created_3_123_,
bobnotif0_.created_date as created_4_123_,
bobnotif0_.document_id as document5_123_,
bobnotif0_.instance_id as instance6_123_,
bobnotif0_.major_version as major_ve7_123_,
bobnotif0_.message as message8_123_,
bobnotif0_.minor_version as minor_ve9_123_,
bobnotif0_.notification as notific10_123_,
bobnotif0_.notification_type as notific11_123_,
bobnotif0_.object_key as object_12_123_,
bobnotif0_.object_record_id as object_13_123_,
bobnotif0_.recipient_id as recipie14_123_,
bobnotif0_.sender_id as sender_15_123_,
bobnotif0_.status as status16_123_,
bobnotif0_.subject as subject17_123_,
bobnotif0_.task_id as task_id18_123_,
bobnotif0_.template_id as templat19_123_,
bobnotif0_.version as version20_123_,
bobnotif0_.workflow_id as workflo21_123_,
bobnotif0_.workflow_step_id as workflo22_123_
from bob_notification bobnotif0_
where bobnotif0_.instance_id=5429
and bobnotif0_.task_id='17952762'
and bobnotif0_.document_id=2924508
order by bobnotif0_.created_date ASC limit 1
TRACE: {
"steps": [
{
"join_preparation": {
"select#": 1,
"steps": [
{
"expanded_query": "/* select#1 */ select `bobnotif0_`.`id` AS `id1_123_`,`bobnotif0_`.`comment` AS `comment2_123_`,`bobnotif0_`.`created_by` AS `created_3_123_`,`bobnotif0_`.`created_date` AS `created_4_123_`,`bobnotif0_`.`document_id` AS `document5_123_`,`bobnotif0_`.`instance_id` AS `instance6_123_`,`bobnotif0_`.`major_version` AS `major_ve7_123_`,`bobnotif0_`.`message` AS `message8_123_`,`bobnotif0_`.`minor_version` AS `minor_ve9_123_`,`bobnotif0_`.`notification` AS `notific10_123_`,`bobnotif0_`.`notification_type` AS `notific11_123_`,`bobnotif0_`.`object_key` AS `object_12_123_`,`bobnotif0_`.`object_record_id` AS `object_13_123_`,`bobnotif0_`.`recipient_id` AS `recipie14_123_`,`bobnotif0_`.`sender_id` AS `sender_15_123_`,`bobnotif0_`.`status` AS `status16_123_`,`bobnotif0_`.`subject` AS `subject17_123_`,`bobnotif0_`.`task_id` AS `task_id18_123_`,`bobnotif0_`.`template_id` AS `templat19_123_`,`bobnotif0_`.`version` AS `version20_123_`,`bobnotif0_`.`workflow_id` AS `workflo21_123_`,`bobnotif0_`.`workflow_step_id` AS `workflo22_123_` from `bob_notification` `bobnotif0_` where ((`bobnotif0_`.`instance_id` = 5429) and (`bobnotif0_`.`task_id` = '17952762') and (`bobnotif0_`.`document_id` = 2924508)) order by `bobnotif0_`.`created_date` limit 1"
}
]
}
},
{
"join_optimization": {
"select#": 1,
"steps": [
{
"condition_processing": {
"condition": "WHERE",
"original_condition": "((`bobnotif0_`.`instance_id` = 5429) and (`bobnotif0_`.`task_id` = '17952762') and (`bobnotif0_`.`document_id` = 2924508))",
"steps": [
{
"transformation": "equality_propagation",
"resulting_condition": "(multiple equal(5429, `bobnotif0_`.`instance_id`) and multiple equal('17952762', `bobnotif0_`.`task_id`) and multiple equal(2924508, `bobnotif0_`.`document_id`))"
},
{
"transformation": "constant_propagation",
"resulting_condition": "(multiple equal(5429, `bobnotif0_`.`instance_id`) and multiple equal('17952762', `bobnotif0_`.`task_id`) and multiple equal(2924508, `bobnotif0_`.`document_id`))"
},
{
"transformation": "trivial_condition_removal",
"resulting_condition": "(multiple equal(5429, `bobnotif0_`.`instance_id`) and multiple equal('17952762', `bobnotif0_`.`task_id`) and multiple equal(2924508, `bobnotif0_`.`document_id`))"
}
]
}
},
{
"substitute_generated_columns": {
}
},
{
"table_dependencies": [
{
"table": "`bob_notification` `bobnotif0_`",
"row_may_be_null": false,
"map_bit": 0,
"depends_on_map_bits": [
]
}
]
},
{
"ref_optimizer_key_uses": [
{
"table": "`bob_notification` `bobnotif0_`",
"field": "document_id",
"equals": "2924508",
"null_rejecting": false
},
{
"table": "`bob_notification` `bobnotif0_`",
"field": "task_id",
"equals": "'17952762'",
"null_rejecting": false
},
{
"table": "`bob_notification` `bobnotif0_`",
"field": "task_id",
"equals": "'17952762'",
"null_rejecting": false
}
]
},
{
"rows_estimation": [
{
"table": "`bob_notification` `bobnotif0_`",
"range_analysis": {
"table_scan": {
"rows": 25061370,
"cost": 3.47e7
},
"potential_range_indexes": [
{
"index": "PRIMARY",
"usable": false,
"cause": "not_applicable"
},
{
"index": "workflow_id",
"usable": false,
"cause": "not_applicable"
},
{
"index": "delete_old",
"usable": false,
"cause": "not_applicable"
},
{
"index": "recipient_id",
"usable": false,
"cause": "not_applicable"
},
{
"index": "task_id",
"usable": true,
"key_parts": [
"document_id",
"task_id",
"created_date",
"id"
]
},
{
"index": "task_id2",
"usable": true,
"key_parts": [
"task_id",
"id"
]
},
{
"index": "object_key",
"usable": false,
"cause": "not_applicable"
}
],
"setup_range_conditions": [
],
"group_index_range": {
"chosen": false,
"cause": "not_group_by_or_distinct"
},
"analyzing_range_alternatives": {
"range_scan_alternatives": [
{
"index": "task_id",
"ranges": [
"2924508 <= document_id <= 2924508 AND 0x0800313739353237363200000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000 <= task_id <= 0x0800313739353237363200000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000"
],
"index_dives_for_eq_ranges": true,
"rowid_ordered": false,
"using_mrr": false,
"index_only": false,
"rows": 8408,
"cost": 10091,
"chosen": true
},
{
"index": "task_id2",
"ranges": [
"0x0800313739353237363200000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000 <= task_id <= 0x0800313739353237363200000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000"
],
"index_dives_for_eq_ranges": true,
"rowid_ordered": true,
"using_mrr": false,
"index_only": false,
"rows": 4701,
"cost": 5642.2,
"chosen": true
}
],
"analyzing_roworder_intersect": {
"usable": false,
"cause": "too_few_roworder_scans"
}
},
"chosen_range_access_summary": {
"range_access_plan": {
"type": "range_scan",
"index": "task_id2",
"rows": 4701,
"ranges": [
"0x0800313739353237363200000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000 <= task_id <= 0x0800313739353237363200000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000"
]
},
"rows_for_plan": 4701,
"cost_for_plan": 5642.2,
"chosen": true
}
}
}
]
},
{
"considered_execution_plans": [
{
"plan_prefix": [
],
"table": "`bob_notification` `bobnotif0_`",
"best_access_path": {
"considered_access_paths": [
{
"access_type": "ref",
"index": "task_id",
"rows": 8408,
"cost": 10090,
"chosen": true
},
{
"access_type": "ref",
"index": "task_id2",
"rows": 4701,
"cost": 5641.2,
"chosen": true
},
{
"access_type": "range",
"range_details": {
"used_index": "task_id2"
},
"chosen": false,
"cause": "heuristic_index_cheaper"
}
]
},
"condition_filtering_pct": 0.0011,
"rows_for_plan": 0.05,
"cost_for_plan": 5641.2,
"chosen": true
}
]
},
{
"attaching_conditions_to_tables": {
"original_condition": "((`bobnotif0_`.`document_id` = 2924508) and (`bobnotif0_`.`task_id` = '17952762') and (`bobnotif0_`.`instance_id` = 5429))",
"attached_conditions_computation": [
],
"attached_conditions_summary": [
{
"table": "`bob_notification` `bobnotif0_`",
"attached": "((`bobnotif0_`.`document_id` = 2924508) and (`bobnotif0_`.`instance_id` = 5429))"
}
]
}
},
{
"clause_processing": {
"clause": "ORDER BY",
"original_clause": "`bobnotif0_`.`created_date`",
"items": [
{
"item": "`bobnotif0_`.`created_date`"
}
],
"resulting_clause_is_simple": true,
"resulting_clause": "`bobnotif0_`.`created_date`"
}
},
{
"added_back_ref_condition": "((`bobnotif0_`.`task_id` <=> '17952762') and ((`bobnotif0_`.`document_id` = 2924508) and (`bobnotif0_`.`instance_id` = 5429)))"
},
{
"reconsidering_access_paths_for_index_ordering": {
"clause": "ORDER BY",
"steps": [
],
"index_order_summary": {
"table": "`bob_notification` `bobnotif0_`",
"index_provides_order": false,
"order_direction": "undefined",
"index": "task_id2",
"plan_changed": false
}
}
},
{
"refine_plan": [
{
"table": "`bob_notification` `bobnotif0_`",
"pushed_index_condition": "(`bobnotif0_`.`task_id` <=> '17952762')",
"table_condition_attached": "((`bobnotif0_`.`document_id` = 2924508) and (`bobnotif0_`.`instance_id` = 5429))"
}
]
}
]
}
},
{
"join_execution": {
"select#": 1,
"steps": [
{
"filesort_information": [
{
"direction": "asc",
"table": "`bob_notification` `bobnotif0_`",
"field": "created_date"
}
],
"filesort_priority_queue_optimization": {
"limit": 1,
"rows_estimate": 7789932171,
"row_size": 13,
"memory_available": 2097152,
"chosen": true
},
"filesort_execution": [
],
"filesort_summary": {
"rows": 2,
"examined_rows": 4701,
"number_of_tmp_files": 0,
"sort_buffer_size": 48,
"sort_mode": ""
}
}
]
}
}
]
}
MISSING_BYTES_BEYOND_MAX_MEM_SIZE: 0
INSUFFICIENT_PRIVILEGES: 0
```
---
### Author:  [@Jamie\_Downs](https://forums.percona.com/u/Jamie_Downs)
#### Post date: [February 16, 2022, 7:11pm UTC](https://forums.percona.com/t/wrong-index-chosen/13263/10 "2022-02-16T19:11:28Z")
I was able to encourage the optimizer to choose the right index by lowering the max\_seeks\_for\_key. It was previously the default. default → set session max\_seeks\_for\_key=18446744073709551615;
Does anyone have any opinion on this system variable?