Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- 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
Add Comment
Please, Sign In to add comment