Hi, I notice a strange behaviour with OPTIMIZE TABLE and ANALYZE TABLE (on MyISAM table) : Before analysing my table, mysql show me : mysql> show index from forumcontpresencepc6; +----------------------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+---------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | |Collation | Cardinality | Sub_part | Packed | Comment | +----------------------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+---------+ | forumcontpresencepc6 | 0 | PRIMARY | 1 | numeropost | A | | NULL | NULL | NULL | | | forumcontpresencepc6 | 1 | date | 1 | date | A | | NULL | NULL | NULL | | | forumcontpresencepc6 | 1 | maxnumrep | 1 | maxnumrep | A | | NULL | NULL | NULL | | +----------------------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+---------+ 3 rows in set (0.00 sec) After analysing the table, this became : mysql> show index from forumcontpresencepc6; +----------------------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+---------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | |Collation | Cardinality | Sub_part | Packed | Comment | +----------------------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+---------+ | forumcontpresencepc6 | 0 | PRIMARY | 1 | numeropost | A | | 217 | NULL | NULL | | | forumcontpresencepc6 | 1 | date | 1 | date | A | | 217 | NULL | NULL | | | forumcontpresencepc6 | 1 | maxnumrep | 1 | maxnumrep | A | | 217 | NULL | NULL | | +----------------------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+---------+ 3 rows in set (0.00 sec) The odd thing is when I execute an optimize table query. My table then become in the same state before analysing the table : +----------------------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+---------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | |Collation | Cardinality | Sub_part | Packed | Comment | +----------------------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+---------+ | forumcontpresencepc6 | 0 | PRIMARY | 1 | numeropost | A | | NULL | NULL | NULL | | | forumcontpresencepc6 | 1 | date | 1 | date | A | | NULL | NULL | NULL | | | forumcontpresencepc6 | 1 | maxnumrep | 1 | maxnumrep | A | | NULL | NULL | NULL | | +----------------------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+---------+ 3 rows in set (0.00 sec) Is this behaviour OK ?? (I was assuming OPTIMIZE TABLE request wasn't resetting the cardinality information) Thanks, Jocelyn Fournier Presence-PC www.presence-pc.com
OPTIMIZE TABLE / ANALYZE TABLE behaviour ?
Fournier Jocelyn [Presence-PC] Mon, 04 Jun 2001 05:00:52 -0700