识别坏索引和/或没有索引（在有大量记录的表上）的查询需要 4-6 分钟才能执行

询问：

SELECT Concat(t.table_schema, '.', t.table_name),
       t.table_rows,
       snu.non_unique,
       smax.cardinality,
       ( t.table_rows / Ifnull(smax.cardinality, 1) )                AS
       "medium distribution",
       t.table_rows * ( t.table_rows / Ifnull(smax.cardinality, 1) ) AS
       "replication row reads"
FROM   information_schema.tables t
       LEFT JOIN (SELECT table_schema,
                         table_name,
                         Max(cardinality) cardinality
                  FROM   information_schema.statistics
                  GROUP  BY table_schema,
                            table_name) AS smax
              ON t.table_schema = smax.table_schema
                 AND t.table_name = smax.table_name
       LEFT JOIN (SELECT table_schema,
                         table_name,
                         Min(non_unique) non_unique
                  FROM   information_schema.statistics
                  GROUP  BY table_schema,
                            table_name) AS snu
              ON t.table_schema = snu.table_schema
                 AND t.table_name = snu.table_name
WHERE  t.table_rows > 0
       AND t.table_schema <> 'information_schema'
       AND t.table_schema <> 'performance_schema'
       AND t.table_schema <> 'mysql'
       AND ( snu.non_unique IS NULL
              OR snu.non_unique = 1 )
       AND ( ( t.table_rows / Ifnull(smax.cardinality, 1) ) > 1.99 )
       AND t.table_rows * ( t.table_rows / Ifnull(smax.cardinality, 1) ) >
           100000
ORDER  BY t.table_rows * ( t.table_rows / Ifnull(smax.cardinality, 1) ) DESC; 

版本：

(none)> show variables like '%version%';
+-------------------------+---------------------------+
| Variable_name           | Value                     |
+-------------------------+---------------------------+
| innodb_version          | 5.6.36-82.1               |
| protocol_version        | 10                        |
| slave_type_conversions  |                           |
| version                 | 10.1.26-MariaDB           |
| version_comment         | Source distribution       |
| version_compile_machine | x86_64                    |
| version_compile_os      | Linux                     |
| version_malloc_library  | system                    |
| version_ssl_library     | OpenSSL 1.0.1f 6 Jan 2014 |
| wsrep_patch_version     | wsrep_25.19               |
+-------------------------+---------------------------+
10 rows in set
Time: 0.010s

解释：

+----+-------------+------------+------+---------------+--------+---------+-------------------------------------------------------------------+--------+----------+--------------------------------------------------------------------------------------+
| id | select_type | table      | type | possible_keys | key    | key_len | ref                                                               | rows   | filtered | Extra                                                                                |
+----+-------------+------------+------+---------------+--------+---------+-------------------------------------------------------------------+--------+----------+--------------------------------------------------------------------------------------+
| 1  | PRIMARY     | t          | ALL  | <null>        | <null> | <null>  | <null>                                                            | <null> | <null>   | Using where; Open_full_table; Scanned all databases; Using temporary; Using filesort |
| 1  | PRIMARY     | <derived2> | ref  | key0          | key0   | 390     | information_schema.t.TABLE_SCHEMA,information_schema.t.TABLE_NAME | 2      | 100.0    | Using where                                                                          |
| 1  | PRIMARY     | <derived3> | ref  | key0          | key0   | 390     | information_schema.t.TABLE_SCHEMA,information_schema.t.TABLE_NAME | 2      | 100.0    | Using where                                                                          |
| 3  | DERIVED     | statistics | ALL  | <null>        | <null> | <null>  | <null>                                                            | <null> | <null>   | Open_frm_only; Scanned all databases; Using temporary; Using filesort                |
| 2  | DERIVED     | statistics | ALL  | <null>        | <null> | <null>  | <null>                                                            | <null> | <null>   | Open_full_table; Scanned all databases; Using temporary; Using filesort              |
+----+-------------+------------+------+---------------+--------+---------+-------------------------------------------------------------------+--------+----------+--------------------------------------------------------------------------------------+
5 rows in set
Time: 0.022s

数数：

> select count('A') from information_schema.tables;
+------------+
| count('A') |
+------------+
| 7846       |
+------------+
1 row in set
Time: 0.069s

看起来无证件Open_full_table; Scanned all databases;需要那么长时间？如何优化此查询或此持续时间在繁忙的服务器上是否正常？