Description:
When an InnoDB table is populated by INSERT INTO ... SELECT or CREATE TABLE ... SELECT (probably other conditions as well), the resulting table statistics is not reliable.
How to repeat:
mysql5536> SHOW CREATE TABLE t1 \G
*************************** 1. row ***************************
Table: t1
Create Table: CREATE TABLE `t1` (
`h_id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`u_id` int(10) unsigned NOT NULL,
`cn_id` int(10) unsigned NOT NULL,
PRIMARY KEY (`h_id`)
) ENGINE=InnoDB AUTO_INCREMENT=87409 DEFAULT CHARSET=latin1
1 row in set (0.00 sec)
mysql5536> SELECT COUNT(*) FROM t1;
+----------+
| COUNT(*) |
+----------+
| 50000 |
+----------+
1 row in set (0.01 sec)
mysql5536> SHOW INDEXES FROM t1;
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| t1 | 0 | PRIMARY | 1 | h_id | A | 50276 | NULL | NULL | | BTREE | | |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
1 row in set (0.00 sec)
mysql5536> CREATE TABLE t2 (h_id BIGINT UNSIGNED NOT NULL, u_id INT UNSIGNED NOT NULL, cn_id INT UNSIGNED NOT NULL, KEY(u_id), KEY(cn_id)) ENGINE=InnoDB;
Query OK, 0 rows affected (0.00 sec)
mysql5536> INSERT INTO t2 SELECT * FROM t1;
Query OK, 50000 rows affected (0.34 sec)
Records: 50000 Duplicates: 0 Warnings: 0
mysql5536> SHOW INDEXES FROM t2;
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| t2 | 1 | u_id | 1 | u_id | A | 50386 | NULL | NULL | | BTREE | | |
| t2 | 1 | cn_id | 1 | cn_id | A | 50386 | NULL | NULL | | BTREE | | |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
2 rows in set (0.00 sec)
mysql5536> SELECT COUNT(*) FROM t2;
+----------+
| COUNT(*) |
+----------+
| 50000 |
+----------+
1 row in set (0.00 sec)
mysql5536> ANALYZE TABLE t2;
+---------+---------+----------+----------+
| Table | Op | Msg_type | Msg_text |
+---------+---------+----------+----------+
| test.t2 | analyze | status | OK |
+---------+---------+----------+----------+
1 row in set (0.00 sec)
mysql5536> SHOW INDEXES FROM t2;
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| t2 | 1 | u_id | 1 | u_id | A | 1749 | NULL | NULL | | BTREE | | |
| t2 | 1 | cn_id | 1 | cn_id | A | 1389 | NULL | NULL | | BTREE | | |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
2 rows in set (0.00 sec)
mysql5536> SHOW GLOBAL VARIABLES LIKE '%stats%';
+---------------------------+---------------+
| Variable_name | Value |
+---------------------------+---------------+
| innodb_stats_method | nulls_equal |
| innodb_stats_on_metadata | ON |
| innodb_stats_sample_pages | 8 |
| myisam_stats_method | nulls_unequal |
+---------------------------+---------------+
4 rows in set (0.00 sec)
mysql5536> CREATE TABLE t3 (h_id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY, u_id INT UNSIGNED NOT NULL, cn_id INT UNSIGNED NOT NULL, KEY(u_id), KEY(cn_id)) ENGINE=InnoDB;
Query OK, 0 rows affected (0.01 sec)
mysql5536> INSERT INTO t3 SELECT * FROM t1;
Query OK, 50000 rows affected (0.35 sec)
Records: 50000 Duplicates: 0 Warnings: 0
mysql5536> SHOW INDEXES FROM t3;
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| t3 | 0 | PRIMARY | 1 | h_id | A | 50276 | NULL | NULL | | BTREE | | |
| t3 | 1 | u_id | 1 | u_id | A | 50276 | NULL | NULL | | BTREE | | |
| t3 | 1 | cn_id | 1 | cn_id | A | 50276 | NULL | NULL | | BTREE | | |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
3 rows in set (0.00 sec)
Suggested fix:
ANALYZE TABLE fixes the cardinality issue.
Description: When an InnoDB table is populated by INSERT INTO ... SELECT or CREATE TABLE ... SELECT (probably other conditions as well), the resulting table statistics is not reliable. How to repeat: mysql5536> SHOW CREATE TABLE t1 \G *************************** 1. row *************************** Table: t1 Create Table: CREATE TABLE `t1` ( `h_id` bigint(20) unsigned NOT NULL AUTO_INCREMENT, `u_id` int(10) unsigned NOT NULL, `cn_id` int(10) unsigned NOT NULL, PRIMARY KEY (`h_id`) ) ENGINE=InnoDB AUTO_INCREMENT=87409 DEFAULT CHARSET=latin1 1 row in set (0.00 sec) mysql5536> SELECT COUNT(*) FROM t1; +----------+ | COUNT(*) | +----------+ | 50000 | +----------+ 1 row in set (0.01 sec) mysql5536> SHOW INDEXES FROM t1; +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | t1 | 0 | PRIMARY | 1 | h_id | A | 50276 | NULL | NULL | | BTREE | | | +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ 1 row in set (0.00 sec) mysql5536> CREATE TABLE t2 (h_id BIGINT UNSIGNED NOT NULL, u_id INT UNSIGNED NOT NULL, cn_id INT UNSIGNED NOT NULL, KEY(u_id), KEY(cn_id)) ENGINE=InnoDB; Query OK, 0 rows affected (0.00 sec) mysql5536> INSERT INTO t2 SELECT * FROM t1; Query OK, 50000 rows affected (0.34 sec) Records: 50000 Duplicates: 0 Warnings: 0 mysql5536> SHOW INDEXES FROM t2; +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | t2 | 1 | u_id | 1 | u_id | A | 50386 | NULL | NULL | | BTREE | | | | t2 | 1 | cn_id | 1 | cn_id | A | 50386 | NULL | NULL | | BTREE | | | +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ 2 rows in set (0.00 sec) mysql5536> SELECT COUNT(*) FROM t2; +----------+ | COUNT(*) | +----------+ | 50000 | +----------+ 1 row in set (0.00 sec) mysql5536> ANALYZE TABLE t2; +---------+---------+----------+----------+ | Table | Op | Msg_type | Msg_text | +---------+---------+----------+----------+ | test.t2 | analyze | status | OK | +---------+---------+----------+----------+ 1 row in set (0.00 sec) mysql5536> SHOW INDEXES FROM t2; +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | t2 | 1 | u_id | 1 | u_id | A | 1749 | NULL | NULL | | BTREE | | | | t2 | 1 | cn_id | 1 | cn_id | A | 1389 | NULL | NULL | | BTREE | | | +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ 2 rows in set (0.00 sec) mysql5536> SHOW GLOBAL VARIABLES LIKE '%stats%'; +---------------------------+---------------+ | Variable_name | Value | +---------------------------+---------------+ | innodb_stats_method | nulls_equal | | innodb_stats_on_metadata | ON | | innodb_stats_sample_pages | 8 | | myisam_stats_method | nulls_unequal | +---------------------------+---------------+ 4 rows in set (0.00 sec) mysql5536> CREATE TABLE t3 (h_id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY, u_id INT UNSIGNED NOT NULL, cn_id INT UNSIGNED NOT NULL, KEY(u_id), KEY(cn_id)) ENGINE=InnoDB; Query OK, 0 rows affected (0.01 sec) mysql5536> INSERT INTO t3 SELECT * FROM t1; Query OK, 50000 rows affected (0.35 sec) Records: 50000 Duplicates: 0 Warnings: 0 mysql5536> SHOW INDEXES FROM t3; +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | t3 | 0 | PRIMARY | 1 | h_id | A | 50276 | NULL | NULL | | BTREE | | | | t3 | 1 | u_id | 1 | u_id | A | 50276 | NULL | NULL | | BTREE | | | | t3 | 1 | cn_id | 1 | cn_id | A | 50276 | NULL | NULL | | BTREE | | | +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ 3 rows in set (0.00 sec) Suggested fix: ANALYZE TABLE fixes the cardinality issue.