MySQL Bugs: #83062: it's no longer possible to determine used key parts by looking at key

Bug #83062	it's no longer possible to determine used key parts by looking at key_length
Submitted:	20 Sep 2016 13:18	Modified:	21 Sep 2016 8:13
Reporter:	Kenny Gryp	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: Optimizer	Severity:	S3 (Non-critical)
Version:	5.6.32, 5.7.14, 5.6.33, 5.7.15	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	explain, MySQL, Optimizer

Description:

However, The documentation states:

The key_len column indicates the length of the key that MySQL decided to use. The length is NULL if the key column says NULL. Note that the value of key_len enables you to determine how many parts of a multiple-part key MySQL actually uses.

https://dev.mysql.com/doc/refman/5.6/en/explain-output.html
https://dev.mysql.com/doc/refman/5.7/en/explain-output.html

This is not true...

How to repeat:

db1-T10 mysql> show create table title\G
*************************** 1. row ***************************
       Table: title
Create Table: CREATE TABLE `title` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `title` text NOT NULL,
  `imdb_index` varchar(12) DEFAULT NULL,
  `kind_id` int(11) NOT NULL,
  `production_year` int(11) DEFAULT NULL,
  `imdb_id` int(11) DEFAULT NULL,
  `phonetic_code` varchar(5) DEFAULT NULL,
  `episode_of_id` int(11) DEFAULT NULL,
  `season_nr` int(11) DEFAULT NULL,
  `episode_nr` int(11) DEFAULT NULL,
  `series_years` varchar(49) DEFAULT NULL,
  `title_crc32` int(10) unsigned DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_title_production_year` (`title`(20),`production_year`)
) ENGINE=InnoDB AUTO_INCREMENT=1543721 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

## DEFAULT with ICP enabled

db1-T10 mysql> explain select * from title where production_year between 1990 and 2000 and title like 'B%'\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: title
         type: range
possible_keys: idx_title_production_year
          key: idx_title_production_year
      key_len: 67
          ref: NULL
         rows: 118030
        Extra: Using index condition; Using where
1 row in set (0.00 sec)

db1-T10 mysql> explain FORMAT=JSON select * from title where production_year between 1990 and 2000 and title like 'B%'\G                     
*************************** 1. row ***************************
EXPLAIN: {
  "query_block": {
    "select_id": 1,
    "table": {
      "table_name": "title",
      "access_type": "range",
      "possible_keys": [
        "idx_title_production_year"
      ],
      "key": "idx_title_production_year",
      "used_key_parts": [
        "title"
      ],
      "key_length": "67",
      "rows": 118030,
      "filtered": 100,
      "index_condition": "(`imdb`.`title`.`production_year` between 1990 and 2000)",
      "attached_condition": "(`imdb`.`title`.`title` like 'B%')"
    }
  }
}
1 row in set, 1 warning (0.00 sec)

db1-T10 mysql> flush status;
Query OK, 0 rows affected (0.00 sec)

db1-T10 mysql> select * from title where production_year between 1990 and 2000 and title like 'B%'\G                                         
...
10557 rows in set (0.06 sec)

db1-T10 mysql> show status like 'ha%';
18 rows in set (0.00 sec)

+----------------------------+-------+
| Variable_name              | Value |
+----------------------------+-------+
| Handler_commit             | 1     |
| Handler_delete             | 0     |
| Handler_discover           | 0     |
| Handler_external_lock      | 2     |
| Handler_mrr_init           | 0     |
| Handler_prepare            | 0     |
| Handler_read_first         | 0     |
| Handler_read_key           | 1     |
| Handler_read_last          | 0     |
| Handler_read_next          | 10557 |
| Handler_read_prev          | 0     |
| Handler_read_rnd           | 0     |
| Handler_read_rnd_next      | 0     |
| Handler_rollback           | 0     |
| Handler_savepoint          | 0     |
| Handler_savepoint_rollback | 0     |
| Handler_update             | 0     |
| Handler_write              | 0     |
+----------------------------+-------+

key_length is 67, used_key_parts is only title, allthough using ICP production_year can also be used.

Here you would think key_length is aware of ICP potential.

### Let's disable ICP:

db1-T10 mysql> set optimizer_switch='index_condition_pushdown=off';
Query OK, 0 rows affected (0.00 sec)

db1-T10 mysql> explain select * from title where production_year between 1990 and 2000 and title like 'B%'\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: title
         type: range
possible_keys: idx_title_production_year
          key: idx_title_production_year
      key_len: 67
          ref: NULL
         rows: 118030
        Extra: Using where
1 row in set (0.00 sec)

db1-T10 mysql> explain format=json select * from title where production_year between 1990 and 2000 and title like 'B%'\G                     
*************************** 1. row ***************************
EXPLAIN: {
  "query_block": {
    "select_id": 1,
    "table": {
      "table_name": "title",
      "access_type": "range",
      "possible_keys": [
        "idx_title_production_year"
      ],
      "key": "idx_title_production_year",
      "used_key_parts": [
        "title"
      ],
      "key_length": "67",
      "rows": 118030,
      "filtered": 100,
      "attached_condition": "((`imdb`.`title`.`production_year` between 1990 and 2000) and (`imdb`.`title`.`title` like 'B%'))"
    }
  }
}
1 row in set, 1 warning (0.00 sec)

Key_length is still 67, even though used_key_parts is only title and no ICP is used.

db1-T10 mysql> flush status;
Query OK, 0 rows affected (0.00 sec)

db1-T10 mysql> select * from title where production_year between 1990 and 2000 and title like 'B%'\G                                         
...
10557 rows in set (0.17 sec)

db1-T10 mysql> pager
Default pager wasn't set, using stdout.
db1-T10 mysql> show session status like 'ha%';
+----------------------------+-------+
| Variable_name              | Value |
+----------------------------+-------+
| Handler_commit             | 1     |
| Handler_delete             | 0     |
| Handler_discover           | 0     |
| Handler_external_lock      | 2     |
| Handler_mrr_init           | 0     |
| Handler_prepare            | 0     |
| Handler_read_first         | 0     |
| Handler_read_key           | 1     |
| Handler_read_last          | 0     |
| Handler_read_next          | 60783 |
| Handler_read_prev          | 0     |
| Handler_read_rnd           | 0     |
| Handler_read_rnd_next      | 0     |
| Handler_rollback           | 0     |
| Handler_savepoint          | 0     |
| Handler_savepoint_rollback | 0     |
| Handler_update             | 0     |
| Handler_write              | 0     |
+----------------------------+-------+
18 rows in set (0.00 sec)

Doublecheck counts:

db1-T10 mysql> select count(*) from title where production_year between 1990 and 2000 and title like 'B%';
+----------+
| count(*) |
+----------+
|    10557 |
+----------+
1 row in set (0.13 sec)

db1-T10 mysql> select count(*) from title where title like 'B%';                                                                             
+----------+
| count(*) |
+----------+
|    60783 |
+----------+
1 row in set (0.12 sec)

And as you can see at handler stats, 60k rows were read, so we were definitely not using production_year.

Suggested fix:
Choose:

- Update documentation stating that key_length can no longer be used to determine how many columns are used in a composite index. 
- Fix key_length so only 62 is returned in above query instead of 67. Also mention if key_length should be ICP aware or not.

Same in MySQL 5.7.14:

db1-T9 mysql> set optimizer_switch='index_condition_pushdown=off';
Query OK, 0 rows affected (0.00 sec)

db1-T9 mysql> explain format=json select * from title where production_year between 1990 and 2000 and title like 'B%'\G
*************************** 1. row ***************************
EXPLAIN: {
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "180648.61"
    },
    "table": {
      "table_name": "title",
      "access_type": "range",
      "possible_keys": [
        "idx_title_production_year"
      ],
      "key": "idx_title_production_year",
      "used_key_parts": [
        "title"
      ],
      "key_length": "67",
      "rows_examined_per_scan": 129034,
      "rows_produced_per_join": 14335,
      "filtered": "11.11",
      "cost_info": {
        "read_cost": "177781.47",
        "eval_cost": "2867.14",
        "prefix_cost": "180648.61",
        "data_read_per_join": "3M"
      },
      "used_columns": [
        "id",
        "title",
        "imdb_index",
        "kind_id",
        "production_year",
        "imdb_id",
        "phonetic_code",
        "episode_of_id",
        "season_nr",
        "episode_nr",
        "series_years",
        "title_crc32"
      ],
      "attached_condition": "((`imdb`.`title`.`production_year` between 1990 and 2000) and (`imdb`.`title`.`title` like 'B%'))"
    }
  }
}
1 row in set, 1 warning (0.00 sec)

Hello Kenny,

Thank you for the report and test case.
Observed this with 5.6.33 build.

Thanks,
Umesh

-- 5.7.15 

[umshastr@hod03]/export/umesh/server/binaries/GABuilds/mysql-advanced-5.7.15: bin/mysql -uroot -S /tmp/mysql_ushastry.sock imdb
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.7.15-enterprise-commercial-advanced MySQL Enterprise Server - Advanced Edition (Commercial)

Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> set optimizer_switch='index_condition_pushdown=off';
Query OK, 0 rows affected (0.00 sec)

mysql>  explain format=json select * from title where production_year between 1990 and 2000 and title like 'B%'\G
*************************** 1. row ***************************
EXPLAIN: {
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "174231.01"
    },
    "table": {
      "table_name": "title",
      "access_type": "range",
      "possible_keys": [
        "idx_title_production_year"
      ],
      "key": "idx_title_production_year",
      "used_key_parts": [
        "title"
      ],
      "key_length": "67",
      "rows_examined_per_scan": 124450,
      "rows_produced_per_join": 13826,
      "filtered": "11.11",
      "cost_info": {
        "read_cost": "171465.73",
        "eval_cost": "2765.28",
        "prefix_cost": "174231.01",
        "data_read_per_join": "3M"
      },
      "used_columns": [
        "id",
        "title",
        "imdb_index",
        "kind_id",
        "production_year",
        "imdb_id",
        "phonetic_code",
        "episode_of_id",
        "season_nr",
        "episode_nr",
        "series_years",
        "title_crc32"
      ],
      "attached_condition": "((`imdb`.`title`.`production_year` between 1990 and 2000) and (`imdb`.`title`.`title` like 'B%'))"
    }
  }
}
1 row in set, 1 warning (0.00 sec)

Any updates on this? I just love telling people that EXPLAIN isn't accurate (/sarcasm).