Menu
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!
906
For example, MySQL can use indexes and ranges to search for NULL with IS NULL .
Cardinality is important — cardinality means the number of distinct values in a column. If you create an index in a column that has low cardinality, that's not going to be beneficial since the index should reduce search space. Low cardinality does not significantly reduce search space.
In MySQL, the term cardinality refers to the uniqueness of data values that can be put into columns. It is a kind of property which influences the ability to search, cluster and sort data. Cardinality can be of two types which are as follows − Low Cardinality − All values for a column must be same.
Conceptually, NULL means “a missing unknown value” and it is treated somewhat differently from other values. Because the result of any arithmetic comparison with NULL is also NULL , you cannot obtain any meaningful results from such comparisons. In MySQL, 0 or NULL means false and anything else means true.
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 --
129
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
