Description:
InnoDB fails to account for the size of primary keys in information_schema and 'show table status' It seems that this also affects the reporting of the size of the implicit primary key when one is not defined.
How to repeat:
mysql> create table t1 (a int) engine='innodb';
Query OK, 0 rows affected (0.02 sec)
mysql> show create table t1\G
*************************** 1. row ***************************
Table: t1
Create Table: CREATE TABLE `t1` (
`a` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)
mysql> insert into t1 values (1),(2),(3);
Query OK, 3 rows affected (0.02 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> show table status\G
*************************** 1. row ***************************
Name: t1
Engine: InnoDB
Version: 10
Row_format: Compact
Rows: 3
Avg_row_length: 5461
Data_length: 16384
Max_data_length: 0
Index_length: 0
Data_free: 732954624
Auto_increment: NULL
Create_time: 2010-10-01 23:04:13
Update_time: NULL
Check_time: NULL
Collation: latin1_swedish_ci
Checksum: NULL
Create_options:
Comment:
1 row in set (0.00 sec)
mysql> select engine, sum(data_length), sum(index_length) from information_schema.tables group by engine;
+--------+------------------+-------------------+
| engine | sum(data_length) | sum(index_length) |
+--------+------------------+-------------------+
| CSV | 0 | 0 |
| InnoDB | 16384 | 0 |
| MEMORY | 0 | 0 |
| MyISAM | 543190 | 100352 |
+--------+------------------+-------------------+
4 rows in set (0.00 sec)
mysql> alter table t1 add primary key (`a`);
Query OK, 3 rows affected (0.02 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> show table status\G
*************************** 1. row ***************************
Name: t1
Engine: InnoDB
Version: 10
Row_format: Compact
Rows: 3
Avg_row_length: 5461
Data_length: 16384
Max_data_length: 0
Index_length: 0
Data_free: 732954624
Auto_increment: NULL
Create_time: 2010-10-01 23:04:54
Update_time: NULL
Check_time: NULL
Collation: latin1_swedish_ci
Checksum: NULL
Create_options:
Comment:
1 row in set (0.00 sec)
mysql> select engine, sum(data_length), sum(index_length) from information_schema.tables group by engine;
+--------+------------------+-------------------+
| engine | sum(data_length) | sum(index_length) |
+--------+------------------+-------------------+
| CSV | 0 | 0 |
| InnoDB | 16384 | 0 |
| MEMORY | 0 | 0 |
| MyISAM | 543190 | 100352 |
+--------+------------------+-------------------+
4 rows in set (0.00 sec)
mysql> alter table t1 add key a_idx (`a`);
Query OK, 0 rows affected (0.04 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> show table status\G
*************************** 1. row ***************************
Name: t1
Engine: InnoDB
Version: 10
Row_format: Compact
Rows: 3
Avg_row_length: 5461
Data_length: 16384
Max_data_length: 0
Index_length: 16384
Data_free: 732954624
Auto_increment: NULL
Create_time: 2010-10-01 23:05:06
Update_time: NULL
Check_time: NULL
Collation: latin1_swedish_ci
Checksum: NULL
Create_options:
Comment:
1 row in set (0.00 sec)
mysql> select engine, sum(data_length), sum(index_length) from information_schema.tables group by engine;
+--------+------------------+-------------------+
| engine | sum(data_length) | sum(index_length) |
+--------+------------------+-------------------+
| CSV | 0 | 0 |
| InnoDB | 16384 | 16384 |
| MEMORY | 0 | 0 |
| MyISAM | 543190 | 100352 |
+--------+------------------+-------------------+
4 rows in set (0.00 sec)
mysql> show create table t1\G
*************************** 1. row ***************************
Table: t1
Create Table: CREATE TABLE `t1` (
`a` int(11) NOT NULL DEFAULT '0',
PRIMARY KEY (`a`),
KEY `a_idx` (`a`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)
Description: InnoDB fails to account for the size of primary keys in information_schema and 'show table status' It seems that this also affects the reporting of the size of the implicit primary key when one is not defined. How to repeat: mysql> create table t1 (a int) engine='innodb'; Query OK, 0 rows affected (0.02 sec) mysql> show create table t1\G *************************** 1. row *************************** Table: t1 Create Table: CREATE TABLE `t1` ( `a` int(11) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1 1 row in set (0.00 sec) mysql> insert into t1 values (1),(2),(3); Query OK, 3 rows affected (0.02 sec) Records: 3 Duplicates: 0 Warnings: 0 mysql> show table status\G *************************** 1. row *************************** Name: t1 Engine: InnoDB Version: 10 Row_format: Compact Rows: 3 Avg_row_length: 5461 Data_length: 16384 Max_data_length: 0 Index_length: 0 Data_free: 732954624 Auto_increment: NULL Create_time: 2010-10-01 23:04:13 Update_time: NULL Check_time: NULL Collation: latin1_swedish_ci Checksum: NULL Create_options: Comment: 1 row in set (0.00 sec) mysql> select engine, sum(data_length), sum(index_length) from information_schema.tables group by engine; +--------+------------------+-------------------+ | engine | sum(data_length) | sum(index_length) | +--------+------------------+-------------------+ | CSV | 0 | 0 | | InnoDB | 16384 | 0 | | MEMORY | 0 | 0 | | MyISAM | 543190 | 100352 | +--------+------------------+-------------------+ 4 rows in set (0.00 sec) mysql> alter table t1 add primary key (`a`); Query OK, 3 rows affected (0.02 sec) Records: 3 Duplicates: 0 Warnings: 0 mysql> show table status\G *************************** 1. row *************************** Name: t1 Engine: InnoDB Version: 10 Row_format: Compact Rows: 3 Avg_row_length: 5461 Data_length: 16384 Max_data_length: 0 Index_length: 0 Data_free: 732954624 Auto_increment: NULL Create_time: 2010-10-01 23:04:54 Update_time: NULL Check_time: NULL Collation: latin1_swedish_ci Checksum: NULL Create_options: Comment: 1 row in set (0.00 sec) mysql> select engine, sum(data_length), sum(index_length) from information_schema.tables group by engine; +--------+------------------+-------------------+ | engine | sum(data_length) | sum(index_length) | +--------+------------------+-------------------+ | CSV | 0 | 0 | | InnoDB | 16384 | 0 | | MEMORY | 0 | 0 | | MyISAM | 543190 | 100352 | +--------+------------------+-------------------+ 4 rows in set (0.00 sec) mysql> alter table t1 add key a_idx (`a`); Query OK, 0 rows affected (0.04 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> show table status\G *************************** 1. row *************************** Name: t1 Engine: InnoDB Version: 10 Row_format: Compact Rows: 3 Avg_row_length: 5461 Data_length: 16384 Max_data_length: 0 Index_length: 16384 Data_free: 732954624 Auto_increment: NULL Create_time: 2010-10-01 23:05:06 Update_time: NULL Check_time: NULL Collation: latin1_swedish_ci Checksum: NULL Create_options: Comment: 1 row in set (0.00 sec) mysql> select engine, sum(data_length), sum(index_length) from information_schema.tables group by engine; +--------+------------------+-------------------+ | engine | sum(data_length) | sum(index_length) | +--------+------------------+-------------------+ | CSV | 0 | 0 | | InnoDB | 16384 | 16384 | | MEMORY | 0 | 0 | | MyISAM | 543190 | 100352 | +--------+------------------+-------------------+ 4 rows in set (0.00 sec) mysql> show create table t1\G *************************** 1. row *************************** Table: t1 Create Table: CREATE TABLE `t1` ( `a` int(11) NOT NULL DEFAULT '0', PRIMARY KEY (`a`), KEY `a_idx` (`a`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 1 row in set (0.00 sec)