MySQL Forums
Forum List  »  Optimizer & Parser

Analyze and cardinality
Posted by: Dorian Kuroki
Date: July 14, 2010 10:50AM

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.

Options: ReplyQuote


Subject
Views
Written By
Posted
Analyze and cardinality
3688
July 14, 2010 10:50AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.