Commands to reproduce: EXPLAIN SELECT t.TABLE_SCHEMA , t.TABLE_NAME , s.INDEX_NAME , s.COLUMN_NAME , s.SEQ_IN_INDEX , s2.max_columns AS `COLS_IN_INDEX` , s.CARDINALITY AS `CARD` , t.TABLE_ROWS AS `ROWS` , ROUND(((s.CARDINALITY / IFNULL(t.TABLE_ROWS, 0.01)) * 100), 2) AS `SEL %` FROM INFORMATION_SCHEMA.STATISTICS s INNER JOIN INFORMATION_SCHEMA.TABLES t ON s.TABLE_SCHEMA = t.TABLE_SCHEMA AND s.TABLE_NAME = t.TABLE_NAME INNER JOIN ( SELECT TABLE_SCHEMA , TABLE_NAME , INDEX_NAME , MAX(SEQ_IN_INDEX) AS max_columns FROM INFORMATION_SCHEMA.STATISTICS WHERE TABLE_SCHEMA != 'mysql' GROUP BY TABLE_SCHEMA, TABLE_NAME, INDEX_NAME ) AS s2 ON s.TABLE_SCHEMA = s2.TABLE_SCHEMA AND s.TABLE_NAME = s2.TABLE_NAME AND s.INDEX_NAME = s2.INDEX_NAME WHERE t.TABLE_SCHEMA != 'mysql' AND t.TABLE_ROWS > 10 AND s.CARDINALITY IS NOT NULL AND (s.CARDINALITY / IFNULL(t.TABLE_ROWS, 0.01)) < 1.00 ORDER BY `SEL %`, TABLE_SCHEMA, TABLE_NAME LIMIT 10\G SELECT t.TABLE_SCHEMA , t.TABLE_NAME , s.INDEX_NAME , s.COLUMN_NAME , s.SEQ_IN_INDEX , s2.max_columns AS `COLS_IN_INDEX` , s.CARDINALITY AS `CARD` , t.TABLE_ROWS AS `ROWS` , ROUND(((s.CARDINALITY / IFNULL(t.TABLE_ROWS, 0.01)) * 100), 2) AS `SEL %` FROM INFORMATION_SCHEMA.STATISTICS s INNER JOIN INFORMATION_SCHEMA.TABLES t ON s.TABLE_SCHEMA = t.TABLE_SCHEMA AND s.TABLE_NAME = t.TABLE_NAME INNER JOIN ( SELECT TABLE_SCHEMA , TABLE_NAME , INDEX_NAME , MAX(SEQ_IN_INDEX) AS max_columns FROM INFORMATION_SCHEMA.STATISTICS WHERE TABLE_SCHEMA != 'mysql' GROUP BY TABLE_SCHEMA, TABLE_NAME, INDEX_NAME ) AS s2 ON s.TABLE_SCHEMA = s2.TABLE_SCHEMA AND s.TABLE_NAME = s2.TABLE_NAME AND s.INDEX_NAME = s2.INDEX_NAME WHERE t.TABLE_SCHEMA != 'mysql' AND t.TABLE_ROWS > 10 AND s.CARDINALITY IS NOT NULL AND (s.CARDINALITY / IFNULL(t.TABLE_ROWS, 0.01)) < 1.00 ORDER BY `SEL %`, TABLE_SCHEMA, TABLE_NAME LIMIT 10; EXPLAIN SELECT t.TABLE_SCHEMA , t.TABLE_NAME , s.INDEX_NAME , s.COLUMN_NAME , s.SEQ_IN_INDEX , ( SELECT MAX(SEQ_IN_INDEX) FROM INFORMATION_SCHEMA.STATISTICS s2 WHERE s.TABLE_SCHEMA = s2.TABLE_SCHEMA AND s.TABLE_NAME = s2.TABLE_NAME AND s.INDEX_NAME = s2.INDEX_NAME ) AS `COLS_IN_INDEX` , s.CARDINALITY AS `CARD` , t.TABLE_ROWS AS `ROWS` , ROUND(((s.CARDINALITY / IFNULL(t.TABLE_ROWS, 0.01)) * 100), 2) AS `SEL %` FROM INFORMATION_SCHEMA.STATISTICS s INNER JOIN INFORMATION_SCHEMA.TABLES t ON s.TABLE_SCHEMA = t.TABLE_SCHEMA AND s.TABLE_NAME = t.TABLE_NAME WHERE t.TABLE_SCHEMA != 'mysql' AND t.TABLE_ROWS > 10 AND s.CARDINALITY IS NOT NULL AND (s.CARDINALITY / IFNULL(t.TABLE_ROWS, 0.01)) < 1.00 ORDER BY `SEL %`, TABLE_SCHEMA, TABLE_NAME LIMIT 10\G SELECT t.TABLE_SCHEMA , t.TABLE_NAME , s.INDEX_NAME , s.COLUMN_NAME , s.SEQ_IN_INDEX , ( SELECT MAX(SEQ_IN_INDEX) FROM INFORMATION_SCHEMA.STATISTICS s2 WHERE s.TABLE_SCHEMA = s2.TABLE_SCHEMA AND s.TABLE_NAME = s2.TABLE_NAME AND s.INDEX_NAME = s2.INDEX_NAME ) AS `COLS_IN_INDEX` , s.CARDINALITY AS `CARD` , t.TABLE_ROWS AS `ROWS` , ROUND(((s.CARDINALITY / IFNULL(t.TABLE_ROWS, 0.01)) * 100), 2) AS `SEL %` FROM INFORMATION_SCHEMA.STATISTICS s INNER JOIN INFORMATION_SCHEMA.TABLES t ON s.TABLE_SCHEMA = t.TABLE_SCHEMA AND s.TABLE_NAME = t.TABLE_NAME WHERE t.TABLE_SCHEMA != 'mysql' AND t.TABLE_ROWS > 10 AND s.CARDINALITY IS NOT NULL AND (s.CARDINALITY / IFNULL(t.TABLE_ROWS, 0.01)) < 1.00 ORDER BY `SEL %`, TABLE_SCHEMA, TABLE_NAME LIMIT 10; $> ./mysql --user=root --port=9306 --socket=../mysql-test/var/tmp/master.sock Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 3 Server version: 6.0.7-alpha-debug-log Source distribution Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | benchmarks | | forge2 | | joinfu | | jpipes_weblog | | mysql | | mysqlforge | | planetmysql | | test | +--------------------+ 9 rows in set (0.05 sec) mysql> EXPLAIN SELECT -> t.TABLE_SCHEMA -> , t.TABLE_NAME -> , s.INDEX_NAME -> , s.COLUMN_NAME -> , s.SEQ_IN_INDEX -> , s2.max_columns AS `COLS_IN_INDEX` -> , s.CARDINALITY AS `CARD` -> , t.TABLE_ROWS AS `ROWS` -> , ROUND(((s.CARDINALITY / IFNULL(t.TABLE_ROWS, 0.01)) * 100), 2) AS `SEL %` -> FROM INFORMATION_SCHEMA.STATISTICS s -> INNER JOIN INFORMATION_SCHEMA.TABLES t -> ON s.TABLE_SCHEMA = t.TABLE_SCHEMA -> AND s.TABLE_NAME = t.TABLE_NAME -> INNER JOIN ( -> SELECT -> TABLE_SCHEMA -> , TABLE_NAME -> , INDEX_NAME -> , MAX(SEQ_IN_INDEX) AS max_columns -> FROM INFORMATION_SCHEMA.STATISTICS -> WHERE TABLE_SCHEMA != 'mysql' -> GROUP BY TABLE_SCHEMA, TABLE_NAME, INDEX_NAME -> ) AS s2 -> ON s.TABLE_SCHEMA = s2.TABLE_SCHEMA -> AND s.TABLE_NAME = s2.TABLE_NAME -> AND s.INDEX_NAME = s2.INDEX_NAME -> WHERE t.TABLE_SCHEMA != 'mysql' -> AND t.TABLE_ROWS > 10 -> AND s.CARDINALITY IS NOT NULL -> AND (s.CARDINALITY / IFNULL(t.TABLE_ROWS, 0.01)) < 1.00 -> ORDER BY `SEL %`, TABLE_SCHEMA, TABLE_NAME -> LIMIT 10\G *************************** 1. row *************************** id: 1 select_type: PRIMARY table: NULL type: NULL possible_keys: NULL key: NULL key_len: NULL ref: NULL rows: NULL Extra: Impossible WHERE noticed after reading const tables *************************** 2. row *************************** id: 2 select_type: DERIVED table: STATISTICS type: ALL possible_keys: NULL key: NULL key_len: NULL ref: NULL rows: NULL Extra: Using where; Open_frm_only; Scanned all databases; Using temporary; Using filesort 2 rows in set (0.02 sec) mysql> mysql> SELECT -> t.TABLE_SCHEMA -> , t.TABLE_NAME -> , s.INDEX_NAME -> , s.COLUMN_NAME -> , s.SEQ_IN_INDEX -> , s2.max_columns AS `COLS_IN_INDEX` -> , s.CARDINALITY AS `CARD` -> , t.TABLE_ROWS AS `ROWS` -> , ROUND(((s.CARDINALITY / IFNULL(t.TABLE_ROWS, 0.01)) * 100), 2) AS `SEL %` -> FROM INFORMATION_SCHEMA.STATISTICS s -> INNER JOIN INFORMATION_SCHEMA.TABLES t -> ON s.TABLE_SCHEMA = t.TABLE_SCHEMA -> AND s.TABLE_NAME = t.TABLE_NAME -> INNER JOIN ( -> SELECT -> TABLE_SCHEMA -> , TABLE_NAME -> , INDEX_NAME -> , MAX(SEQ_IN_INDEX) AS max_columns -> FROM INFORMATION_SCHEMA.STATISTICS -> WHERE TABLE_SCHEMA != 'mysql' -> GROUP BY TABLE_SCHEMA, TABLE_NAME, INDEX_NAME -> ) AS s2 -> ON s.TABLE_SCHEMA = s2.TABLE_SCHEMA -> AND s.TABLE_NAME = s2.TABLE_NAME -> AND s.INDEX_NAME = s2.INDEX_NAME -> WHERE t.TABLE_SCHEMA != 'mysql' -> AND t.TABLE_ROWS > 10 -> AND s.CARDINALITY IS NOT NULL -> AND (s.CARDINALITY / IFNULL(t.TABLE_ROWS, 0.01)) < 1.00 -> ORDER BY `SEL %`, TABLE_SCHEMA, TABLE_NAME -> LIMIT 10; +---------------+------------------+---------------------+-------------+--------------+---------------+------+-------+-------+ | TABLE_SCHEMA | TABLE_NAME | INDEX_NAME | COLUMN_NAME | SEQ_IN_INDEX | COLS_IN_INDEX | CARD | ROWS | SEL % | +---------------+------------------+---------------------+-------------+--------------+---------------+------+-------+-------+ | mysqlforge | WLTask | title | title | 1 | 2 | 1 | 1196 | 0.08 | | mysqlforge | WLTask | title | description | 2 | 2 | 1 | 1196 | 0.08 | | jpipes_weblog | s9y_comments | commstat_idx | status | 1 | 1 | 1 | 837 | 0.12 | | jpipes_weblog | s9y_comments | commtype_idx | type | 1 | 1 | 1 | 837 | 0.12 | | mysqlforge | mw_recentchanges | new_name_timestamp | rc_new | 1 | 3 | 4 | 1833 | 0.22 | | mysqlforge | mw_externallinks | el_from | el_from | 1 | 2 | 133 | 48801 | 0.27 | | mysqlforge | WLTask | ix_priority | priority | 1 | 1 | 4 | 1196 | 0.33 | | mysqlforge | ProjectText | ft_description_name | name | 1 | 2 | 1 | 243 | 0.41 | | mysqlforge | ProjectText | ft_description_name | description | 2 | 2 | 1 | 243 | 0.41 | | jpipes_weblog | s9y_entries | entry_idx | extended | 3 | 3 | 1 | 229 | 0.44 | +---------------+------------------+---------------------+-------------+--------------+---------------+------+-------+-------+ 10 rows in set (1.48 sec) mysql> mysql> EXPLAIN SELECT -> t.TABLE_SCHEMA -> , t.TABLE_NAME -> , s.INDEX_NAME -> , s.COLUMN_NAME -> , s.SEQ_IN_INDEX -> , ( -> SELECT MAX(SEQ_IN_INDEX) -> FROM INFORMATION_SCHEMA.STATISTICS s2 -> WHERE s.TABLE_SCHEMA = s2.TABLE_SCHEMA -> AND s.TABLE_NAME = s2.TABLE_NAME -> AND s.INDEX_NAME = s2.INDEX_NAME -> ) AS `COLS_IN_INDEX` -> , s.CARDINALITY AS `CARD` -> , t.TABLE_ROWS AS `ROWS` -> , ROUND(((s.CARDINALITY / IFNULL(t.TABLE_ROWS, 0.01)) * 100), 2) AS `SEL %` -> FROM INFORMATION_SCHEMA.STATISTICS s -> INNER JOIN INFORMATION_SCHEMA.TABLES t -> ON s.TABLE_SCHEMA = t.TABLE_SCHEMA -> AND s.TABLE_NAME = t.TABLE_NAME -> WHERE t.TABLE_SCHEMA != 'mysql' -> AND t.TABLE_ROWS > 10 -> AND s.CARDINALITY IS NOT NULL -> AND (s.CARDINALITY / IFNULL(t.TABLE_ROWS, 0.01)) < 1.00 -> ORDER BY `SEL %`, TABLE_SCHEMA, TABLE_NAME -> LIMIT 10\G *************************** 1. row *************************** id: 1 select_type: PRIMARY table: s type: ALL possible_keys: NULL key: NULL key_len: NULL ref: NULL rows: NULL Extra: Using where; Open_full_table; Scanned all databases; Using temporary; Using filesort *************************** 2. row *************************** id: 1 select_type: PRIMARY table: t type: ALL possible_keys: NULL key: NULL key_len: NULL ref: NULL rows: NULL Extra: Using where; Open_full_table; Scanned all databases; Using join buffer *************************** 3. row *************************** id: 2 select_type: DEPENDENT SUBQUERY table: s2 type: ALL possible_keys: NULL key: NULL key_len: NULL ref: NULL rows: NULL Extra: Using where; Open_frm_only; Scanned all databases 3 rows in set (0.03 sec) mysql> mysql> SELECT -> t.TABLE_SCHEMA -> , t.TABLE_NAME -> , s.INDEX_NAME -> , s.COLUMN_NAME -> , s.SEQ_IN_INDEX -> , ( -> SELECT MAX(SEQ_IN_INDEX) -> FROM INFORMATION_SCHEMA.STATISTICS s2 -> WHERE s.TABLE_SCHEMA = s2.TABLE_SCHEMA -> AND s.TABLE_NAME = s2.TABLE_NAME -> AND s.INDEX_NAME = s2.INDEX_NAME -> ) AS `COLS_IN_INDEX` -> , s.CARDINALITY AS `CARD` -> , t.TABLE_ROWS AS `ROWS` -> , ROUND(((s.CARDINALITY / IFNULL(t.TABLE_ROWS, 0.01)) * 100), 2) AS `SEL %` -> FROM INFORMATION_SCHEMA.STATISTICS s -> INNER JOIN INFORMATION_SCHEMA.TABLES t -> ON s.TABLE_SCHEMA = t.TABLE_SCHEMA -> AND s.TABLE_NAME = t.TABLE_NAME -> WHERE t.TABLE_SCHEMA != 'mysql' -> AND t.TABLE_ROWS > 10 -> AND s.CARDINALITY IS NOT NULL -> AND (s.CARDINALITY / IFNULL(t.TABLE_ROWS, 0.01)) < 1.00 -> ORDER BY `SEL %`, TABLE_SCHEMA, TABLE_NAME -> LIMIT 10; +---------------+------------------+--------------------+---------------+--------------+---------------+------+-------+-------+ | TABLE_SCHEMA | TABLE_NAME | INDEX_NAME | COLUMN_NAME | SEQ_IN_INDEX | COLS_IN_INDEX | CARD | ROWS | SEL % | +---------------+------------------+--------------------+---------------+--------------+---------------+------+-------+-------+ | mysqlforge | mw_logging | user_time | log_user | 1 | 2 | 1 | 1996 | 0.05 | | mysqlforge | WLTask | title | title | 1 | 2 | 1 | 1196 | 0.08 | | mysqlforge | WLTask | title | description | 2 | 2 | 1 | 1196 | 0.08 | | mysqlforge | mw_recentchanges | new_name_timestamp | rc_new | 1 | 3 | 2 | 1911 | 0.10 | | jpipes_weblog | s9y_comments | commtype_idx | type | 1 | 1 | 1 | 837 | 0.12 | | jpipes_weblog | s9y_comments | commstat_idx | status | 1 | 1 | 1 | 837 | 0.12 | | mysqlforge | mw_externallinks | el_from | el_from | 1 | 2 | 68 | 44063 | 0.15 | | mysqlforge | WLTask | ix_priority | priority | 1 | 1 | 4 | 1196 | 0.33 | | mysqlforge | mw_logging | page_time | log_namespace | 1 | 3 | 7 | 1996 | 0.35 | | mysqlforge | mw_logging | type_time | log_type | 1 | 2 | 7 | 1996 | 0.35 | +---------------+------------------+--------------------+---------------+--------------+---------------+------+-------+-------+ 10 rows in set (1 min 22.10 sec) mysql>