Description:
MySQL documentation says regarding key_len in EXPLAIN output: " 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. ", so I would expect for single-column key this would be same as data storage requirement, however for nullable columns I see extra byte in this field.
Data storage requirement does not mention anything for NULL-able columns for non-NDB tables also (http://dev.mysql.com/doc/refman/5.7/en/storage-requirements.html)
How to repeat:
mysql> create table foo(f1 int, key(f1));
Query OK, 0 rows affected (0,05 sec
mysql> insert into foo values(1),(2),(3);
Query OK, 3 rows affected (0,00 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> explain select * from foo where f1=1\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: foo
type: ref
possible_keys: f1
key: f1
key_len: 5
ref: const
rows: 1
Extra: Using where; Using index
1 row in set (0,02 sec)
I expect key_len will be 4 since INT takes 4 bytes, but key_len shows 5. If I make column f1 NOT NULL key_len will be 4 as expected:
mysql> create table bar(f1 int not null, key(f1));
Query OK, 0 rows affected (0,03 sec)
mysql> insert into bar values(1),(2),(3);
Query OK, 3 rows affected (0,00 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> explain select * from bar where f1=1\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: bar
type: ref
possible_keys: f1
key: f1
key_len: 4
ref: const
rows: 1
Extra: Using index
1 row in set (0,00 sec)
