MySQL Forums
Forum List  »  Optimizer & Parser

Analyze question
Posted by: Dorian Kuroki
Date: July 13, 2010 03:30PM

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?

Many thanks.

Dorian.

Options: ReplyQuote


Subject
Views
Written By
Posted
Analyze question
4057
July 13, 2010 03:30PM
1286
July 14, 2010 10:48PM
1521
July 15, 2010 11:26AM
1265
July 15, 2010 08:54PM


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.