Bug #92724 | Optimizer is using wrong index that isn't in possible index list | ||
---|---|---|---|
Submitted: | 9 Oct 2018 21:33 | Modified: | 10 Nov 2018 11:12 |
Reporter: | monty solomon | Email Updates: | |
Status: | No Feedback | Impact on me: | |
Category: | MySQL Server: Optimizer | Severity: | S3 (Non-critical) |
Version: | 5.7.21, 5.7.23 | OS: | CentOS |
Assigned to: | CPU Architecture: | Any |
[9 Oct 2018 21:33]
monty solomon
[9 Oct 2018 21:35]
monty solomon
mysql> EXPLAIN SELECT tId, rId, updatedAt, lTS, runId FROM t WHERE rId = 'Some-string' ORDER BY startedAt DESC LIMIT 1 OFFSET 0\G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: t partitions: NULL type: index possible_keys: rId_2,startedAt,lTS,dId,host,purged key: startedAt3 key_len: 5 ref: NULL rows: 5803 filtered: 2.34 Extra: Using where 1 row in set, 1 warning (0.00 sec) mysql> EXPLAIN FORMAT=JSON SELECT tId, rId, updatedAt, lTS, runId FROM t WHERE rId = 'Some-string' ORDER BY startedAt DESC LIMIT 1 OFFSET 0\G *************************** 1. row *************************** EXPLAIN: { "query_block": { "select_id": 1, "cost_info": { "query_cost": "183640.80"=utf8 ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=8 }, "ordering_operation": { "using_filesort": false, "table": { "table_name": "t", "access_type": "index", "possible_keys": [ "rId_2", "startedAt", "lTS", "dId", "host", "purged" ], "key": "startedAt3", "used_key_parts": [ "startedAt" ], "key_lengt": "5", "rows_examined_per_scan": 5802, "rows_produced_per_join": 153034, "filtered": "2.34", "cost_info": { "read_cost": "153034.00", "eval_cost": "30606.80", "prefix_cost": "183640.80", "data_read_per_join": "249M" }, "used_columns": [ "tId", "rId", "lTS", "updatedAt", "runId", "startedAt" ], "attached_condition": "((`s`.`t`.`rId` <=> 'Some-string'))" } } } } 1 row in set, 1 warning (0.00 sec)
[9 Oct 2018 21:42]
monty solomon
When using the correct index mysql> EXPLAIN SELECT tId, rId, updatedAt, lTS, runId FROM t USE INDEX(startedAt) WHERE rId = 'Some-string' ORDER BY startedAt DESC LIMIT 1 OFFSET 0\G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: t partitions: NULL type: ref possible_keys: startedAt key: startedAt key_len: 302 ref: const rows: 257792 filtered: 100.00 Extra: Using where 1 row in set, 1 warning (0.00 sec) mysql> EXPLAIN FORMAT=JSON SELECT tId, rId, updatedAt, lTS, runId FROM t USE INDEX(startedAt) WHERE rId = 'Some-string' ORDER BY startedAt DESC LIMIT 1 OFFSET 0\G *************************** 1. row *************************** EXPLAIN: { "query_block": { "select_id": 1, "cost_info": { "query_cost": "309350.40" }, "ordering_operation": { "using_filesort": false, "table": { "table_name": "t", "access_type": "ref", "possible_keys": [ "startedAt" ], "key": "startedAt", "used_key_parts": [ "rId" ], "key_length": "302", "ref": [ "const" ], "rows_examined_per_scan": 257792, "rows_produced_per_join": 257792, "filtered": "100.00", "cost_info": { "read_cost": "257792.00", "eval_cost": "51558.40", "prefix_cost": "309350.40", "data_read_per_join": "420M" }, "used_columns": [ "tId", "rId", "lTS", "updatedAt", "runId", "startedAt" ], "attached_condition": "((`s`.`t`.`rId` <=> 'Some-string'))" } } } } 1 row in set, 1 warning (0.00 sec)
[9 Oct 2018 21:45]
monty solomon
It does choose the correct index for another value mysql> EXPLAIN SELECT tId, rId, updatedAt, lTS, runId FROM t WHERE rId = 'SomeOther-string' ORDER BY startedAt DESC LIMIT 1 OFFSET 0\G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: t partitions: NULL type: ref possible_keys: rId_2,startedAt,lTS,dId,host,purged key: startedAt key_len: 302 ref: const rows: 342420 filtered: 100.00 Extra: Using where 1 row in set, 1 warning (0.08 sec) mysql> EXPLAIN FORMAT=JSON SELECT tId, rId, updatedAt, lTS, runId FROM t WHERE rId = 'SomeOther-string' ORDER BY startedAt DESC LIMIT 1 OFFSET 0\G *************************** 1. row *************************** EXPLAIN: { "query_block": { "select_id": 1, "cost_info": { "query_cost": "410904.00" }, "ordering_operation": { "using_filesort": false, "table": { "table_name": "t", "access_type": "ref", "possible_keys": [ "rId_2", "startedAt", "lTS", "dId", "host", "purged" ], "key": "startedAt", "used_key_parts": [ "rId" ], "key_length": "302", "ref": [ "const" ], "rows_examined_per_scan": 342420, "rows_produced_per_join": 342420, "filtered": "100.00", "cost_info": { "read_cost": "342420.00", "eval_cost": "68484.00", "prefix_cost": "410904.00", "data_read_per_join": "559M" }, "used_columns": [ "tId", "rId", "lTS", "updatedAt", "runId", "startedAt" ], "attached_condition": "((`s`.`t`.`rId` <=> 'SomeOther-string'))" } } } } 1 row in set, 1 warning (0.00 sec)
[9 Oct 2018 21:46]
monty solomon
mysql> show table status like 't'\G *************************** 1. row *************************** Name: t Engine: InnoDB Version: 10 Row_format: Compressed Rows: 11688574 Avg_row_length: 7291 Data_length: 85224071168 Max_data_length: 0 Index_length: 38046957568 Data_free: 5959581696 Auto_increment: NULL Create_time: 2018-04-05 14:56:11 Update_time: 2018-10-09 21:37:42 Check_time: NULL Collation: utf8_general_ci Checksum: NULL Create_options: row_format=COMPRESSED KEY_BLOCK_SIZE=8 Comment:
[9 Oct 2018 21:49]
monty solomon
mysql> show index from t\G *************************** 1. row *************************** Table: t Non_unique: 0 Key_name: PRIMARY Seq_in_index: 1 Column_name: tId Collation: A Cardinality: 11688574 Sub_part: NULL Packed: NULL Null: Index_type: BTREE Comment: Index_comment: *************************** 2. row *************************** Table: t Non_unique: 1 Key_name: rId_2 Seq_in_index: 1 Column_name: rId Collation: A Cardinality: 435282 Sub_part: NULL Packed: NULL Null: Index_type: BTREE Comment: Index_comment: *************************** 3. row *************************** Table: t Non_unique: 1 Key_name: rId_2 Seq_in_index: 2 Column_name: updatedAt Collation: A Cardinality: 11688574 Sub_part: NULL Packed: NULL Null: Index_type: BTREE Comment: Index_comment: *************************** 4. row *************************** Table: t Non_unique: 1 Key_name: runId Seq_in_index: 1 Column_name: runId Collation: A Cardinality: 663009 Sub_part: NULL Packed: NULL Null: YES Index_type: BTREE Comment: Index_comment: *************************** 5. row *************************** Table: t Non_unique: 1 Key_name: runId Seq_in_index: 2 Column_name: rId Collation: A Cardinality: 360549 Sub_part: NULL Packed: NULL Null: Index_type: BTREE Comment: Index_comment: *************************** 6. row *************************** Table: t Non_unique: 1 Key_name: startedAt Seq_in_index: 1 Column_name: rId Collation: A Cardinality: 382933 Sub_part: NULL Packed: NULL Null: Index_type: BTREE Comment: Index_comment: *************************** 7. row *************************** Table: t Non_unique: 1 Key_name: startedAt Seq_in_index: 2 Column_name: startedAt Collation: A Cardinality: 11688574 Sub_part: NULL Packed: NULL Null: YES Index_type: BTREE Comment: Index_comment: *************************** 8. row *************************** Table: t Non_unique: 1 Key_name: lTS Seq_in_index: 1 Column_name: rId Collation: A Cardinality: 307796 Sub_part: NULL Packed: NULL Null: Index_type: BTREE Comment: Index_comment: *************************** 9. row *************************** Table: t Non_unique: 1 Key_name: lTS Seq_in_index: 2 Column_name: lTS Collation: A Cardinality: 492473 Sub_part: NULL Packed: NULL Null: YES Index_type: BTREE Comment: Index_comment: *************************** 10. row *************************** Table: t Non_unique: 1 Key_name: lTS Seq_in_index: 3 Column_name: startedAt Collation: A Cardinality: 11688574 Sub_part: NULL Packed: NULL Null: YES Index_type: BTREE Comment: Index_comment: *************************** 11. row *************************** Table: t Non_unique: 1 Key_name: dId Seq_in_index: 1 Column_name: rId Collation: A Cardinality: 401298 Sub_part: NULL Packed: NULL Null: Index_type: BTREE Comment: Index_comment: *************************** 12. row *************************** Table: t Non_unique: 1 Key_name: dId Seq_in_index: 2 Column_name: dId Collation: A Cardinality: 2079024 Sub_part: NULL Packed: NULL Null: YES Index_type: BTREE Comment: Index_comment: *************************** 13. row *************************** Table: t Non_unique: 1 Key_name: dId Seq_in_index: 3 Column_name: startedAt Collation: A Cardinality: 11688574 Sub_part: NULL Packed: NULL Null: YES Index_type: BTREE Comment: Index_comment: *************************** 14. row *************************** Table: t Non_unique: 1 Key_name: host Seq_in_index: 1 Column_name: rId Collation: A Cardinality: 720933 Sub_part: NULL Packed: NULL Null: Index_type: BTREE Comment: Index_comment: *************************** 15. row *************************** Table: t Non_unique: 1 Key_name: host Seq_in_index: 2 Column_name: host Collation: A Cardinality: 3377680 Sub_part: NULL Packed: NULL Null: YES Index_type: BTREE Comment: Index_comment: *************************** 16. row *************************** Table: t Non_unique: 1 Key_name: host Seq_in_index: 3 Column_name: startedAt Collation: A Cardinality: 11688574 Sub_part: NULL Packed: NULL Null: YES Index_type: BTREE Comment: Index_comment: *************************** 17. row *************************** Table: t Non_unique: 1 Key_name: host2 Seq_in_index: 1 Column_name: host Collation: A Cardinality: 454453 Sub_part: NULL Packed: NULL Null: YES Index_type: BTREE Comment: Index_comment: *************************** 18. row *************************** Table: t Non_unique: 1 Key_name: updatedAt Seq_in_index: 1 Column_name: updatedAt Collation: A Cardinality: 11688574 Sub_part: NULL Packed: NULL Null: Index_type: BTREE Comment: Index_comment: *************************** 19. row *************************** Table: t Non_unique: 1 Key_name: updatedAt Seq_in_index: 2 Column_name: rId Collation: A Cardinality: 11688574 Sub_part: NULL Packed: NULL Null: Index_type: BTREE Comment: Index_comment: *************************** 20. row *************************** Table: t Non_unique: 1 Key_name: purged Seq_in_index: 1 Column_name: rId Collation: A Cardinality: 214810 Sub_part: NULL Packed: NULL Null: Index_type: BTREE Comment: Index_comment: *************************** 21. row *************************** Table: t Non_unique: 1 Key_name: purged Seq_in_index: 2 Column_name: purged Collation: A Cardinality: 184475 Sub_part: NULL Packed: NULL Null: Index_type: BTREE Comment: Index_comment: *************************** 22. row *************************** Table: t Non_unique: 1 Key_name: purged Seq_in_index: 3 Column_name: updatedAt Collation: A Cardinality: 11688574 Sub_part: NULL Packed: NULL Null: Index_type: BTREE Comment: Index_comment: *************************** 23. row *************************** Table: t Non_unique: 1 Key_name: startedAt3 Seq_in_index: 1 Column_name: startedAt Collation: A Cardinality: 5607285 Sub_part: NULL Packed: NULL Null: YES Index_type: BTREE Comment: Index_comment: 23 rows in set (0.00 sec)
[10 Oct 2018 6:23]
monty solomon
Added 5.7.23
[10 Oct 2018 11:12]
MySQL Verification Team
Hi Monty, This truly looks like a bug. We do not know whether it is fixed in 8.0, but we definitely would need to test it on both 5.7 and 8.0. Hence, what we need is a dump of the table in question. You do not need to update all rows, but just a minimal number to repeat the behaviour. When uploading, please use "Files" tab and your data will be protected. Thanks very much in advance.
[11 Nov 2018 1:00]
Bugs System
No feedback was provided for this bug for over a month, so it is being suspended automatically. If you are able to provide the information that was originally requested, please do so and change the status of the bug back to "Open".