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:
None 
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
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
[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".