MySQL 5.6 quickly locates unreasonable Indexes

MySQL 5.6 quickly locates unreasonable Indexes

# If CARDINALITY/TABLE_ROWS is less than 10% (experience value), it indicates a high data repetition rate. Generally, you need to consider whether it is necessary to create this index.

######################################## #################
# Mysql 5.6
Currently, the Cardinality STATISTICS in the 5.6 STATISTICS table is incorrect !!! VisibleMySQL bugs #78066.
However, the Cardinality statistics in the innodb_index_stats table are still correct.
######################################## #################
SELECT
T. TABLE_SCHEMA,
T. TABLE_NAME, INDEX_NAME,
S. CARDINALITY,
T. TABLE_ROWS,
S. CARDINALITY/t. TABLE_ROWS AS selecti.pdf
FROM
Information_schema.TABLES t,
(SELECT database_name, table_name, index_name, stat_value AS CARDINALITY
FROM mysql. innodb_index_stats
WHERE (database_name, table_name, index_name, stat_name) IN
(SELECT table_schema, table_name, index_name, CONCAT ('n' _ diff_pfx0 ', MAX (seq_in_index ))
FROM information_schema.STATISTICS
-- Where table_name = 'xxxxx'
Group by table_schema, table_name, index_name) s
WHERE t. table_schema = s. database_name
AND t. table_name = s. table_name
AND t. table_rows! = 0
AND t. table_schema not in ('mysql', 'performance _ scheme', 'information _ scheme ')
Order by selecti.pdf;

From: http://mp.weixin.qq.com/s? _ Biz = MjM5MjIxNDA4NA ==& mid = 401131835 & idx = 1 & sn = 37c5fd9d3d8670fb1_a1e0565e50eeb & scene = 0 # wechat_redirect
######################################## #####################
# Mysql 5.7 table STATISTICS records the CARDINALITY values of each index
######################################## #####################

SELECT
T. TABLE_SCHEMA, t. TABLE_NAME, INDEX_NAME, CARDINALITY,
TABLE_ROWS, CARDINALITY/TABLE_ROWS AS selecti.pdf
FROM
Information_schema.TABLES t,
(
SELECT table_schema, table_name, index_name, cardinality
FROM information_schema.STATISTICS
WHERE (table_schema, table_name, index_name, seq_in_index) IN (
SELECT table_schema, table_name, index_name, MAX (seq_in_index)
FROM information_schema.STATISTICS
Group by table_schema, table_name, index_name)
) S
WHERE
T. table_schema = s. table_schema
AND t. table_name = s. table_name AND t. table_rows! = 0
AND t. table_schema not in ('mysql', 'performance _ scheme', 'information _ scheme ')
Order by selecti.pdf;

This article permanently updates the link address: