Description:
ANALYZE table doesn't updates index cardinality in temporary tables, while in MyISAM tables it does.
How to repeat:
mysql> create temporary table a(a int, index(a));
Query OK, 0 rows affected (0.00 sec)
mysql> insert into a values('1'),('2'),('3'),('4'),('5');
Query OK, 5 rows affected (0.00 sec)
Records: 5 Duplicates: 0 Warnings: 0
mysql> show index from a\G;
*************************** 1. row ***************************
Table: a
Non_unique: 1
Key_name: a
Seq_in_index: 1
Column_name: a
Collation: A
Cardinality: NULL
Sub_part: NULL
Packed: NULL
Null: YES
Index_type: BTREE
Comment:
mysql> analyze table a;
+--------+---------+----------+----------+
| Table | Op | Msg_type | Msg_text |
+--------+---------+----------+----------+
| test.a | analyze | status | OK |
+--------+---------+----------+----------+
1 row in set (0.00 sec)
mysql> show index from a\G;
*************************** 1. row ***************************
Table: a
Non_unique: 1
Key_name: a
Seq_in_index: 1
Column_name: a
Collation: A
Cardinality: NULL
Sub_part: NULL
Packed: NULL
Null: YES
Index_type: BTREE
Comment:
1 row in set (0.00 sec)
mysql> drop table a;
Query OK, 0 rows affected (0.00 sec)
mysql> create table a(a int, index(a));
Query OK, 0 rows affected (0.03 sec)
mysql> insert into a values('1'),('2'),('3'),('4'),('5');
Query OK, 5 rows affected (0.00 sec)
Records: 5 Duplicates: 0 Warnings: 0
mysql> show index from a\G;
*************************** 1. row ***************************
Table: a
Non_unique: 1
Key_name: a
Seq_in_index: 1
Column_name: a
Collation: A
Cardinality: NULL
Sub_part: NULL
Packed: NULL
Null: YES
Index_type: BTREE
Comment:
1 row in set (0.00 sec)
mysql> analyze table a;
+--------+---------+----------+----------+
| Table | Op | Msg_type | Msg_text |
+--------+---------+----------+----------+
| test.a | analyze | status | OK |
+--------+---------+----------+----------+
1 row in set (0.00 sec)
mysql> show index from a\G;
*************************** 1. row ***************************
Table: a
Non_unique: 1
Key_name: a
Seq_in_index: 1
Column_name: a
Collation: A
Cardinality: 5
Sub_part: NULL
Packed: NULL
Null: YES
Index_type: BTREE
Comment:
1 row in set (0.00 sec)
BTW OPTIMIZE table works for temporary tables
Suggested fix:
Fix this or document.
Description: ANALYZE table doesn't updates index cardinality in temporary tables, while in MyISAM tables it does. How to repeat: mysql> create temporary table a(a int, index(a)); Query OK, 0 rows affected (0.00 sec) mysql> insert into a values('1'),('2'),('3'),('4'),('5'); Query OK, 5 rows affected (0.00 sec) Records: 5 Duplicates: 0 Warnings: 0 mysql> show index from a\G; *************************** 1. row *************************** Table: a Non_unique: 1 Key_name: a Seq_in_index: 1 Column_name: a Collation: A Cardinality: NULL Sub_part: NULL Packed: NULL Null: YES Index_type: BTREE Comment: mysql> analyze table a; +--------+---------+----------+----------+ | Table | Op | Msg_type | Msg_text | +--------+---------+----------+----------+ | test.a | analyze | status | OK | +--------+---------+----------+----------+ 1 row in set (0.00 sec) mysql> show index from a\G; *************************** 1. row *************************** Table: a Non_unique: 1 Key_name: a Seq_in_index: 1 Column_name: a Collation: A Cardinality: NULL Sub_part: NULL Packed: NULL Null: YES Index_type: BTREE Comment: 1 row in set (0.00 sec) mysql> drop table a; Query OK, 0 rows affected (0.00 sec) mysql> create table a(a int, index(a)); Query OK, 0 rows affected (0.03 sec) mysql> insert into a values('1'),('2'),('3'),('4'),('5'); Query OK, 5 rows affected (0.00 sec) Records: 5 Duplicates: 0 Warnings: 0 mysql> show index from a\G; *************************** 1. row *************************** Table: a Non_unique: 1 Key_name: a Seq_in_index: 1 Column_name: a Collation: A Cardinality: NULL Sub_part: NULL Packed: NULL Null: YES Index_type: BTREE Comment: 1 row in set (0.00 sec) mysql> analyze table a; +--------+---------+----------+----------+ | Table | Op | Msg_type | Msg_text | +--------+---------+----------+----------+ | test.a | analyze | status | OK | +--------+---------+----------+----------+ 1 row in set (0.00 sec) mysql> show index from a\G; *************************** 1. row *************************** Table: a Non_unique: 1 Key_name: a Seq_in_index: 1 Column_name: a Collation: A Cardinality: 5 Sub_part: NULL Packed: NULL Null: YES Index_type: BTREE Comment: 1 row in set (0.00 sec) BTW OPTIMIZE table works for temporary tables Suggested fix: Fix this or document.