MySQL: Index Length zero bytes (innodb table)

Question

I have an INNODB table containing about 700M rows.

i created a new empty compressed tables without indexes and after the data import i've re-created 7 indexes

Now executing SHOW TABLE STATUS i see Index length zero bytes :

name: mytable

Engine: InnoDB

Row format: Compressed

Rows: 691027999

Data_length: 24464850944 (22,78 GB)

Index_length: 0

Data_free: 2621440

The IDB file is not 22,78Gb but instead 130Gb and indexes works well.

I tried a restart but nothing change.

Why MySQL will not update the correct index length?

(I use Percona 5.6)

[Solved] i fix it with ANALYZE TABLE

Answer 1

This behavior seems a bug for MySQL and it can be easily reproduced.

SQL:

drop table tbl;
create table tbl(c1 int, c2 int);
create index i1 on tbl(c1,c2);
show table status like 'tbl';
drop table tbl;
create table tbl(c1 int, c2 int, key i1(c1,c2));
show table status like 'tbl';

Output:

mysql> create table tbl(c1 int, c2 int);
Query OK, 0 rows affected (0.01 sec)

mysql> create index i1 on tbl(c1,c2);
show table status likQuery OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show table status like 'tbl';
+------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+--------------------+----------+----------------+---------+
| Name | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time         | Update_time | Check_time | Collation          | Checksum | Create_options | Comment |
+------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+--------------------+----------+----------------+---------+
| tbl  | InnoDB |      10 | Compact    |    0 |              0 |       16384 |               0 |            0 |         0 |           NULL | 2016-02-20 22:39:31 | NULL        | NULL       | utf8mb4_general_ci |     NULL |                |         |
+------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+--------------------+----------+----------------+---------+
1 row in set (0.00 sec)

mysql> drop table tbl;
Query OK, 0 rows affected (0.00 sec)

mysql> create table tbl(c1 int, c2 int, key i1(c1,c2));
 status like 'tbl';
Query OK, 0 rows affected (0.01 sec)

mysql> show table status like 'tbl';
+------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+--------------------+----------+----------------+---------+
| Name | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time         | Update_time | Check_time | Collation          | Checksum | Create_options | Comment |
+------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+--------------------+----------+----------------+---------+
| tbl  | InnoDB |      10 | Compact    |    0 |              0 |       16384 |               0 |        16384 |         0 |           NULL | 2016-02-20 22:39:31 | NULL        | NULL       | utf8mb4_general_ci |     NULL |                |         |
+------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+--------------------+----------+----------------+---------+
1 row in set (0.00 sec)

mysql> select version();
+-----------+
| version() |
+-----------+
| 5.7.8-rc  |
+-----------+
1 row in set (0.00 sec)