Find MySQL indexes without cardinality

Question

I've recently found a few queries in my slow query log that should have been using indexes. When investigating the indexes in phpmyadmin it shows a null or empty cardinality. I'm unsure what causes this, but I need to find a way to identify this problem without manually checking 200+ tables.

Is there a script or a query that I can use to find these "corrupt" indexes? If so can I force a rebuild of the index?

score 4 · Accepted Answer · edited Mar 21 '13 at 00:09

You can use INFORMATION_SCHEMA.STATISTICS to find the offending indexes:

SELECT table_schema,table_name,index_name FROM information_schema.statistics
WHERE CARDINALITY IS NULL AND SEQ_IN_INDEX = 1 AND INDEX_TYPE <> 'FULLTEXT'
AND table_schema NOT IN ('information_schema','mysql');

You can use this query to create the script to run ANALYZE TABLE on those tables:

SELECT CONCAT('ANALYZE TABLE ',db,'.',tb,';') FROM
(SELECT table_schema db, table_name tb FROM information_schema.statistics
WHERE CARDINALITY IS NULL AND SEQ_IN_INDEX = 1 AND INDEX_TYPE <> 'FULLTEXT'
AND table_schema NOT IN ('information_schema','mysql')) A;

Here is how to use the query to make and execute the index statistics update:

SQLSTMT="SELECT CONCAT('ANALYZE TABLE ',db,'.',tb,';') FROM"
SQLSTMT="${SQLSTMT} (SELECT table_schema db, table_name tb FROM "
SQLSTMT="${SQLSTMT} information_schema.statistics"
SQLSTMT="${SQLSTMT} WHERE CARDINALITY IS NULL AND SEQ_IN_INDEX = 1"
SQLSTMT="${SQLSTMT} AND INDEX_TYPE <> 'FULLTEXT'"
SQLSTMT="${SQLSTMT} AND table_schema NOT IN ('information_schema','mysql')) A"
mysql -u... -p... -ANe"${SQLSTMT}" > AnalyzeTablesWithNoCardinalities.sql
mysql -u... -p... < AnalyzeTablesWithNoCardinalities

CAVEAT

Please keep in mind that not all levels within an index may have a cardinality. Note all that I only chose SEQ_IN_INDEX = 1 means I only looked at indexes whose first indexed column has no cardinality. That may apply to PRIMARY KEY columns in some instances.

Thanks that helps a lot. I added to your first query to remove any tables without rows (as they more often an not have NULL cardinality indexes as well). Comments won't let me post the query so I'll post the follow up query in an answer. — Noodles, Mar 20 '13 at 20:21

Noodles · Answer 2 · 2013-03-20T23:57:54.237

3

Building on what Rolando posted above. This cuts out any empty tables from the list of NULL index cardinality.

SELECT s.table_schema, s.table_name, s.index_name
FROM information_schema.statistics AS s
INNER JOIN information_schema.tables AS t ON s.table_schema = t.table_schema AND s.table_name = t.table_name
WHERE t.TABLE_ROWS <> 0 AND s.CARDINALITY IS NULL AND s.SEQ_IN_INDEX = 1 AND s.INDEX_TYPE <> 'FULLTEXT'
AND s.table_schema NOT IN ('information_schema','mysql') AND t.ENGINE <> 'MEMORY';

edited Mar 20 '13 at 23:57

answered Mar 20 '13 at 20:22

Noodles

1,356
3
18
29

Have removed MEMORY tables as they incorrectly report indexes without cardinality when using BTREE indexes (see MySQL bug #58520). – Noodles Mar 20 '13 at 23:58

Find MySQL indexes without cardinality

2 Answers2

CAVEAT