Is NULL cardinality in an index a problem - MySQL 5.x

Question

I'm having performance problems on the live version of a system which I can't reproduce locally.

In comparing a few EXPLAIN results on my local copies of the database with the live one, I note that multi-field indexes aren't used in some places on the live but are locally, and further investigation shows that these indexes have a cardinality of NULL on the live.

I'm guessing this is the problem, but what does NULL cardinality mean and will it cause an index not to be used? Will an Optimize fix this and is there a means of preventing it recurring? I don't have full access to the live MySQL database so Analyze and Optimize are outside my normal capabilities.

Many thanks for any replies!

Corbin

Does this seem relevant: bugs.mysql.com/bug.php?id=40983 – Wellstacked

Answer 1

NULL cardinality on an MyISAM table's index occurs when a table is created (or truncated) and populated with data. AFTER the load the user MUST perform an 'Analyze Table x' to effectively gain cardinality for that table. A similar issue exists in InnoDB tables, where the 'Analyze Table x' needs to be performed periodically to ensure optimal index performance. The MySQL database engine does NOT automatically update index cardinality, with exception of single-column Primary Keys.

Non-NULL cardinality is critical/required for MySQL to utilize that index.

A bit about cardinality: It is a measure of the uniqueness of a field. The more unique (higher value) the more effective an index on that field will be and fewer records will be touched. NULL cardinality is not the same as a 0 (zero) cardinality.
Read This: SHOW INDEX

-- JJ --

Antagonize

Answer 2

As per fsb's useful find, I will guess that you are using MyISAM, and would suggest that you move to InnoDB, which has been considered a superior table engine for some years now.

One of InnoDB's many features is automatic maintenance of indexes.

Brian