Bug #79688 Manual does NOT explain Data_length vs Index_length properly for InnoDB tables
Submitted: 17 Dec 2015 10:34 Modified: 17 Feb 2016 19:14
Reporter: Valeriy Kravchuk Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Documentation Severity:S3 (Non-critical)
Version:any OS:Any
Assigned to: Daniel Price CPU Architecture:Any
Tags: information_schema, innodb, missing manual, show table status

[17 Dec 2015 10:34] Valeriy Kravchuk
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.
[17 Dec 2015 11:31] Peter Laursen
See also http://bugs.mysql.com/bug.php?id=74338
[17 Dec 2015 11:53] MySQL Verification Team
Thank you for the bug report.
[17 Feb 2016 19:14] Daniel Price
Posted by developer:
 
Data_length and Index_length field descriptions have been updated. The revisions should appear online within 24 hours.

http://dev.mysql.com/doc/refman/5.7/en/show-table-status.html

Thank you for the bug report.