Mysql – Find MySQL indexes without cardinality

indexesMySQL

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?

Best Answer

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.

Related Topic