Wrong index chosen

MySQL & MariaDB Percona Server for MySQL 5.7
1

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. (How to Update InnoDB Table Statistics Manually - Percona Database Performance Blog).

Please can anyone suggest anything I might have missed?

1 Like
2

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

1 Like
3

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?

1 Like
4

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

1 Like
5

Hi Carlos, I should have been clearer.
I copied the statistics from the good server to the bad server and that didn’t help.

1 Like
6

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?

1 Like
7

Apologies Matthew. I did not see you comment back in December. I will collect the trace information and share it.

1 Like
8

Hello Matthew please find optimizer info below. This is for the slow version.

+----+-------------+--------------+------------+-------+------------------+------------+---------+------+------+----------+-------------+
| 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
1 Like
9

For completeness. this is the fast version.

+----+-------------+--------------+------------+------+------------------+----------+---------+-------+------+----------+----------------------------------------------------+
| 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": "<sort_key, rowid>"
            }
          }
        ]
      }
    }
  ]
}
MISSING_BYTES_BEYOND_MAX_MEM_SIZE: 0
          INSUFFICIENT_PRIVILEGES: 0
1 Like
10

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?

1 Like