CODEWITHSUNDEEP
mysqlperformanceindexingcardinality
LinuxGuru
LinuxGuru

Reputation: 365

What causes MySQL cardinality to decrease?

In our production MySQL systems we have some queries that normally will take less then 2 seconds to execute but some times they will run for over a minute. Every time one of these queries runs long we check the indexing and find that the cardinality has dropped on the same VARCHAR(25) field. We run MySQL as a cluster of master with multiple slaves and we will find that the cardinality on just that slow system with have decreased, the value will remain high on all other servers. When the value drops it will go from about 20k-30k (high value) down to only a few hundred. Running the ANALYZE TABLE command fixes the cardinality and brings it backup to 20k-30k and the query runs fast again.

I have started monitoring the cardinality on all tables to see how this value changes over time. I understand that cardinality will go up as new data is added, and I would expect it to go down as records are deleted but this table very rarely gets records deleted yet the value will go up and down every time I look at it.

MySQL 5.5.8 InnoDB

CentOS 5.7

Any ideas on what I should be looking for? COUT(*) = 402259

Upvotes: 1

Views: 463

Answers (1)

Jehad Keriaki
Jehad Keriaki

Reputation: 545

The stats of InnoDB tables are approximate, not accurate. So, if you want to know the exact cardinality of a field, you may run this query:

SELECT COUNT(DISTINCT field_name) FROM table_name

Upvotes: 1

Related Questions