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.