Find MySQL indexes without cardinality

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.

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';