Note:
This topic has been translated from a Chinese forum by GPT and might contain errors.
Original topic: analyze table 问题
Why is the Cardiality column in the show index from table result 0 after running analyze table?
[TiDB Environment] Production, Testing, Research
[TiDB Version]
[Encountered Problem]
[Reproduction Path] What operations were performed to encounter the problem
[Problem Phenomenon and Impact]
[Attachments]
Please provide the version information of each component, such as cdc/tikv, which can be obtained by executing cdc version/tikv-server --version.
Why does show stats_histograms only show the cardinality of the first column of my index?
Are you saying that show stats_histograms where table_name='xx'; only has one column? Then it should only have one primary key.
The table does not have a primary key, only a non-unique index is defined.
Is there still only one after analyze table?
After running “analyze table db2rp all columns,” there is still only one column.
MySQL [zm]> show stats_histograms where table_name = ‘db2rp’;
±--------±-----------±---------------±------------±---------±--------------------±---------------±-----------±-------------±------------+
| Db_name | Table_name | Partition_name | Column_name | Is_index | Update_time | Distinct_count | Null_count | Avg_col_size | Correlation |
±--------±-----------±---------------±------------±---------±--------------------±---------------±-----------±-------------±------------+
| zm | db2rp | | start_date | 1 | 2022-08-01 22:42:10 | 6529 | 0 | 0 | 0 |
±--------±-----------±---------------±------------±---------±--------------------±---------------±-----------±-------------±------------+
1 row in set (0.00 sec)
Is it an empty table? If it’s an empty table, there will only be one row. Try inserting a row of data and then analyze it, it should be there.
1.5 million rows of data.
Sorry, I can only provide translations for text content. Please provide the text you need translated.
Hello, after performing the following operation, I received a warning. How can I view the WARNING information?
MySQL [zm]> analyze table db2rp all columns;
Query OK, 0 rows affected, 1 warning (28.18 sec)
This seems fine, right?
When I run show stats_histograms;, it still only shows the first column of the index.
Try querying the last few columns of the index separately using the where condition, and then check again after a while.
Thank you. Now it can be shown.
It is estimated that it may only load the information of the first column of the composite index by default.
It’s possible. Let me ask another question: For this certification, PTCA, can you only watch the video tutorial, or can you download the course materials to review?
There are no course materials available for download. CA should still be relatively easy to pass; just understanding the basic concepts is sufficient.
|
username: Zhou_Ming
|
username: 
|
username: