jpipes@shakedown:~/dev/mysql-5.0/client$ ./mysql --user=root --socket=../mysql-test/var/tmp/master.sock
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.0.42-debug-log Source distribution

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> show databases;
+---------------------+
| Database            |
+---------------------+
| information_schema  | 
| auto_increment_test | 
| enwrite             | 
| enwrite_tests2      | 
| habari              | 
| hiddenagent         | 
| jpipes_weblog       | 
| mysql               | 
| mysql_community     | 
| mysqlforge          | 
| mysqlforge2         | 
| old_blog            | 
| planetmysql         | 
| sakila              | 
| test                | 
| web                 | 
| worklog             | 
+---------------------+
17 rows in set (0.00 sec)

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 % |
+---------------+----------------+------------------------------+-------------------+--------------+---------------+------+------+-------+
| enwrite       | entries        | ix_current_revision          | current_revision  |            1 |             1 |    1 |  113 |  0.88 | 
| enwrite       | entries        | fk_entries_authors           | author            |            1 |             1 |    1 |  113 |  0.88 | 
| jpipes_weblog | mw_brokenlinks | bl_to                        | bl_to             |            1 |             1 |   18 |   37 | 48.65 | 
| jpipes_weblog | mw_cur         | user_timestamp               | cur_user          |            1 |             2 |    7 |  967 |  0.72 | 
| jpipes_weblog | mw_cur         | namespace_redirect_timestamp | cur_namespace     |            1 |             3 |    3 |  967 |  0.31 | 
| jpipes_weblog | mw_cur         | cur_timestamp                | cur_timestamp     |            1 |             1 |   38 |  967 |  3.93 | 
| jpipes_weblog | mw_cur         | user_timestamp               | inverse_timestamp |            2 |             2 |   38 |  967 |  3.93 | 
| jpipes_weblog | mw_cur         | namespace_redirect_timestamp | cur_is_redirect   |            2 |             3 |    5 |  967 |  0.52 | 
| jpipes_weblog | mw_cur         | cur_random                   | cur_random        |            1 |             1 |   37 |  967 |  3.83 | 
| jpipes_weblog | mw_cur         | usertext_timestamp           | cur_user_text     |            1 |             2 |   10 |  967 |  1.03 | 
+---------------+----------------+------------------------------+-------------------+--------------+---------------+------+------+-------+
10 rows in set (2 min 22.26 sec)

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;
+----+--------------------+-------+------+---------------+------+---------+------+------+----------------------------------------------+
| id | select_type        | table | type | possible_keys | key  | key_len | ref  | rows | Extra                                        |
+----+--------------------+-------+------+---------------+------+---------+------+------+----------------------------------------------+
|  1 | PRIMARY            | s     | ALL  | NULL          | NULL | NULL    | NULL |    2 | Using where; Using temporary; Using filesort | 
|  1 | PRIMARY            | t     | ALL  | NULL          | NULL | NULL    | NULL |    2 | Using where                                  | 
|  2 | DEPENDENT SUBQUERY | s2    | ALL  | NULL          | NULL | NULL    | NULL |    2 | Using where                                  | 
+----+--------------------+-------+------+---------------+------+---------+------+------+----------------------------------------------+
3 rows in set (0.01 sec)

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 % |
+---------------+---------------------+------------------------------+-------------------+--------------+---------------+------+------+-------+
| enwrite       | entries             | fk_entries_authors           | author            |            1 |             1 |    2 |  113 |  1.77 | 
| enwrite       | entries             | ix_current_revision          | current_revision  |            1 |             1 |    2 |  113 |  1.77 | 
| enwrite       | entry_revision_text | PRIMARY                      | entry             |            1 |             2 |  190 |  194 | 97.94 | 
| enwrite       | entry_revision_text | PRIMARY                      | revision          |            2 |             2 |  190 |  194 | 97.94 | 
| jpipes_weblog | mw_brokenlinks      | bl_to                        | bl_to             |            1 |             1 |   18 |   37 | 48.65 | 
| jpipes_weblog | mw_cur              | cur_timestamp                | cur_timestamp     |            1 |             1 |   38 |  967 |  3.93 | 
| jpipes_weblog | mw_cur              | user_timestamp               | inverse_timestamp |            2 |             2 |   38 |  967 |  3.93 | 
| jpipes_weblog | mw_cur              | namespace_redirect_timestamp | cur_is_redirect   |            2 |             3 |    5 |  967 |  0.52 | 
| jpipes_weblog | mw_cur              | cur_random                   | cur_random        |            1 |             1 |   37 |  967 |  3.83 | 
| jpipes_weblog | mw_cur              | usertext_timestamp           | cur_user_text     |            1 |             2 |   10 |  967 |  1.03 | 
+---------------+---------------------+------------------------------+-------------------+--------------+---------------+------+------+-------+
10 rows in set (2.23 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;
+----+-------------+------------+------+---------------+------+---------+------+------+-----------------------------------------------------+
| id | select_type | table      | type | possible_keys | key  | key_len | ref  | rows | Extra                                               |
+----+-------------+------------+------+---------------+------+---------+------+------+-----------------------------------------------------+
|  1 | PRIMARY     | NULL       | NULL | NULL          | NULL | NULL    | NULL | NULL | Impossible WHERE noticed after reading const tables | 
|  2 | DERIVED     | STATISTICS | ALL  | NULL          | NULL | NULL    | NULL |    2 | Using where; Using temporary; Using filesort        | 
+----+-------------+------------+------+---------------+------+---------+------+------+-----------------------------------------------------+
2 rows in set (0.01 sec)