List:General Discussion« Previous MessageNext Message »
From:Fournier Jocelyn [Presence-PC] Date:June 4 2001 12:18pm
Subject:OPTIMIZE TABLE / ANALYZE TABLE behaviour ?
View as plain text  
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


Thread
OPTIMIZE TABLE / ANALYZE TABLE behaviour ?Fournier Jocelyn [Presence-PC]4 Jun