Why did my MySQL Index Cardinality get Zeroed out?

Question

I have an older web application that uses a MySQL Database (MYISAM). I noticed recently that the performance of the application was drastically reduced. After checking on my indexes, I noticed that the cardinality for all of them was reporting zero.

I was able to fix this by doing an ANALYZE TABLE on each table.

But I'm curious, what causes the cardinality to get zeroed out in the first place?

Answer 1

From memory, some repair table operations wipe the statistics and you need to ANALYZE TABLE to get them back.

Other than that, I can't think of any reason why they should be zeroed. I'd just expect that they'd be out of date.