Analyze and cardinality

[Dorian Kuroki]

Hello,

I have a doubt about how analyze command determines an index cardinality. My mysql version is 5.0.77.
I have an innodb table ( called test )with 47000000 ( aprox. ) lines.
this table has several indexes.
when I run "analyze table test " and then " show indexes from test" , I can see that the value " cardinality " of the indexes , not are always the same , in some cases the value is too much different. For example:

mysql> analyze table test;
+------------------------+---------+----------+----------+
| Table | Op | Msg_type | Msg_text |
+------------------------+---------+----------+----------+
| testdb | analyze | status | OK |
+------------------------+---------+----------+----------+
1 row in set (0.58 sec)
mysql> show indexes from test;
+-----------+------------+--------------------------------+--------------+--------------------------+-----------+-------------+----------+--------+------+------------+---------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+-----------+------------+--------------------------------+--------------+--------------------------+-----------+-------------+----------+--------+------+------------+---------+
| test | 0 | PRIMARY | 1 | id_test | A | 46936164 | NULL | NULL | | BTREE | |
............
| test | 1 | IDX_test_5 | 1 | id_repart | A | 17 | NULL | NULL | YES | BTREE | |
......
+-----------+------------+--------------------------------+--------------+--------------------------+-----------+-------------+----------+--------+------+------------+---------+
14 rows in set (0.62 sec)

In this case the cardinality was 17. But in the following analyze


mysql> analyze table test;
+------------------------+---------+----------+----------+
| Table | Op | Msg_type | Msg_text |
+------------------------+---------+----------+----------+
| testdb | analyze | status | OK |
+------------------------+---------+----------+----------+
1 row in set (0.58 sec)
mysql> show indexes from test;
+-----------+------------+--------------------------------+--------------+--------------------------+-----------+-------------+----------+--------+------+------------+---------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+-----------+------------+--------------------------------+--------------+--------------------------+-----------+-------------+----------+--------+------+------------+---------+
| test | 0 | PRIMARY | 1 | id_test | A | 46936164 | NULL | NULL | | BTREE | |
............
| test | 1 | IDX_test_5 | 1 | id_repart | A | 20404 | NULL | NULL | YES | BTREE | |
......
+-----------+------------+--------------------------------+--------------+--------------------------+-----------+-------------+----------+--------+------+------------+---------+
14 rows in set (0.62 sec)


Now, the cardinality was 20404.

the real distinct values of this column is 11305 ( obtained with a select count (distinct ... )
If I will continue running analyze --> show indexes , I have different values ( sometimes near to 0 , sometimes no ).
If my optimizer are based on this value to determine the best plan... I have a problem....because 17 is too much different than 20404.

Can I configure any parameter to controlling this? How can I explain this behavior?
Recently, I have post this issue in the general forum, but I believe that is more appropriate to do so in this.


Many thanks.

Dorian.