Description:
Manual for SHOW TABLE STATUS (http://dev.mysql.com/doc/refman/5.7/en/show-table-status.html) says:
"Data_length
The length of the data file.
Max_data_length
The maximum length of the data file. This is the total number of bytes of data that can be stored in the table, given the data pointer size used.
Index_length
The length of the index file.
Data_free
The number of allocated but unused bytes.
This information is also shown for InnoDB tables (previously, it was in the Comment value). InnoDB tables report the free space of the tablespace to which the table belongs. For a table located in the shared tablespace, this is the free space of the shared tablespace. If you are using multiple tablespaces and the table has its own tablespace, the free space is for only that table. Free space means the number of bytes in completely free extents minus a safety margin. Even if free space displays as 0, it may be possible to insert rows as long as new extents need not be allocated."
So, while it goes into a lot of details for Data_free in case of InnoDB tables, it still says just "The length of the data file." for Data_length and "The length of the index file" for Index_length. Both are far from complete and correct for InnoDB tables, as there are no separate data file and index file, to begin with. We speak about size of data (PRIMARY key) vs size of secondary indexes here.
Manual page for INFORMATION_SCHEMA.TABLES just says nothing about these columns, check http://dev.mysql.com/doc/refman/5.7/en/tables-table.html.
How to repeat:
Try to explain based on the manual what "data file" and "index file" are related to Data_length and Index_length in the following primitive case:
mysql> create table ti(id int primary key, c1 int, key(c1));
Query OK, 0 rows affected (0.26 sec)
mysql> insert into ti values(1,1),(2,2);
Query OK, 2 rows affected (0.06 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> show table status like 'ti'\G
*************************** 1. row ***************************
Name: ti
Engine: InnoDB
Version: 10
Row_format: Compact
Rows: 2
Avg_row_length: 8192
Data_length: 16384
Max_data_length: 0
Index_length: 16384
Data_free: 17825792
Auto_increment: NULL
Create_time: 2015-12-17 12:30:34
Update_time: NULL
Check_time: NULL
Collation: utf8_general_ci
Checksum: NULL
Create_options:
Comment:
1 row in set (0.05 sec)
mysql> alter table ti drop key c1;
Query OK, 0 rows affected (0.17 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> show table status like 'ti'\G
*************************** 1. row ***************************
Name: ti
Engine: InnoDB
Version: 10
Row_format: Compact
Rows: 2
Avg_row_length: 8192
Data_length: 16384
Max_data_length: 0
Index_length: 0
Data_free: 17825792
Auto_increment: NULL
Create_time: 2015-12-17 12:30:34
Update_time: NULL
Check_time: NULL
Collation: utf8_general_ci
Checksum: NULL
Create_options:
Comment:
1 row in set (0.01 sec)
mysql> show create table ti\G
*************************** 1. row ***************************
Table: ti
Create Table: CREATE TABLE `ti` (
`id` int(11) NOT NULL,
`c1` int(11) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)
Suggested fix:
Clearly and explicitly document what are the values of Data_length and Index_length in case of InnoDB tables. We all know what is it, and can explain with tests etc, but it's a shame to have this NOT documented explicitly in otherwise mostly fine manual.
Users keeps asking about the interpretation of columns in SHOW TABLE STATUS, but we can not just give them URL to the manual, as it's misleading at best in case of InnoDB.