SELECT
t.table_schema AS db,
t.table_name AS tab_name,
s.index_name AS index_name,
s.column_name AS field_name,
s.seq_in_index AS seq_in_index,
s2.max_columns AS max_col,
s.cardinality AS cardinality,
t.table_rows AS table_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 > 100
AND s.CARDINALITY IS NOT NULL
AND (s.CARDINALITY / IFNULL(t.TABLE_ROWS, 0.01)) < 1.00
ORDER BY sel, s.table_schema, s.table_name
LIMIT 10
t.table_schema AS db,
t.table_name AS tab_name,
s.index_name AS index_name,
s.column_name AS field_name,
s.seq_in_index AS seq_in_index,
s2.max_columns AS max_col,
s.cardinality AS cardinality,
t.table_rows AS table_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 > 100
AND s.CARDINALITY IS NOT NULL
AND (s.CARDINALITY / IFNULL(t.TABLE_ROWS, 0.01)) < 1.00
ORDER BY sel, s.table_schema, s.table_name
LIMIT 10
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/133735/viewspace-732013/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/133735/viewspace-732013/