MySQL Forums :: Optimizer & Parser :: Analyze question


Advanced Search

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 3953 Dorian Kuroki 07/13/2010 03:30PM
Re: Analyze question 1267 Rick James 07/14/2010 10:48PM
Re: Analyze question 1486 Dorian Kuroki 07/15/2010 11:26AM
Re: Analyze question 1244 Rick James 07/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.