| 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: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".
Description: We noticed some queries were taking between 30-120 seconds to execute instead of a fraction of a second. The EXPLAIN output showed the optimizer choose the wrong index. The chosen index wasn't in the list of possible keys. mysql> SELECT tId, rId, updatedAt, lTS, runId FROM t WHERE rId = 'Some-string' ORDER BY startedAt DESC LIMIT 1 OFFSET 0\G *************************** 1. row *************************** 1 row in set (1 min 52.74 sec) mysql> 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 *************************** 1 row in set (0.00 sec) How to repeat: mysql> show create table t\G *************************** 1. row *************************** Table: t Create Table: CREATE TABLE `t` ( `tId` varchar(200) NOT NULL DEFAULT '', `rId` varchar(100) NOT NULL, `lTS` varchar(25) DEFAULT NULL, `updatedAt` timestamp NOT NULL DEFAULT '1971-01-01 00:00:01', `bytes` mediumblob NOT NULL, `runId` varchar(100) DEFAULT NULL, `dId` varchar(100) DEFAULT NULL, `host` varchar(100) CHARACTER SET ascii DEFAULT NULL, `startedAt` timestamp NULL DEFAULT NULL, `purged` tinyint(1) NOT NULL DEFAULT '0', PRIMARY KEY (`tId`), KEY `rId_2` (`rId`,`updatedAt`), KEY `runId` (`runId`,`rId`), KEY `startedAt` (`rId`,`startedAt`), KEY `lTS` (`rId`,`lTS`,`startedAt`), KEY `dId` (`rId`,`dId`,`startedAt`), KEY `host` (`rId`,`host`,`startedAt`), KEY `host2` (`host`), KEY `updatedAt` (`updatedAt`,`rId`), KEY `purged` (`rId`,`purged`,`updatedAt`), KEY `startedAt3` (`startedAt`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=8