Bug #56439 Call chk_key() upon any DDL operations
Submitted: 1 Sep 2010 9:09
Reporter: Mikiya Okuno Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: MyISAM storage engine Severity:S4 (Feature request)
Version:5.1 OS:Any
Assigned to: CPU Architecture:Any

[1 Sep 2010 9:09] Mikiya Okuno
Description:
When we use MyISAM, we should firstly initialize index statistics using ANALYZE TABLE after adding index. Unless executing ANALYZE TABLE, index statistics isn't updated upon DML operations.

How to repeat:
mysql> create table t (a int, index(a)) engine myisam;
Query OK, 0 rows affected (0.10 sec)

mysql> insert into t values(1),(2),(3);
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> show index in t\G
*************************** 1. row ***************************
       Table: t
  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)

Like this, cardinality field is firstly NULL. It's enabled after issuing ANALYZE/REPAIR/CHECK TABLE statements.

Suggested fix:
Call chk_key() after every DDL statement operations.