1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
|
--
-- View: schema_redundant_keys
--
-- Shows indexes which are made redundant (or duplicate) by other (dominant) keys.
--
-- mysql> select * from sys.schema_redundant_indexes\G
-- *************************** 1. row ***************************
-- table_schema: test
-- table_name: rkey
-- redundant_index_name: j
-- redundant_index_columns: j
-- redundant_index_non_unique: 1
-- dominant_index_name: j_2
-- dominant_index_columns: j,k
-- dominant_index_non_unique: 1
-- subpart_exists: 0
-- sql_drop_index: ALTER TABLE `test`.`rkey` DROP INDEX `j`
-- 1 row in set (0.20 sec)
--
-- mysql> SHOW CREATE TABLE test.rkey\G
-- *************************** 1. row ***************************
-- Table: rkey
-- Create Table: CREATE TABLE `rkey` (
-- `i` int(11) NOT NULL,
-- `j` int(11) DEFAULT NULL,
-- `k` int(11) DEFAULT NULL,
-- PRIMARY KEY (`i`),
-- KEY `j` (`j`),
-- KEY `j_2` (`j`,`k`)
-- ) ENGINE=InnoDB DEFAULT CHARSET=latin1
-- 1 row in set (0.06 sec)
--
CREATE OR REPLACE
ALGORITHM = TEMPTABLE
DEFINER = 'mariadb.sys'@'localhost'
SQL SECURITY INVOKER
VIEW schema_redundant_indexes (
table_schema,
table_name,
redundant_index_name,
redundant_index_columns,
redundant_index_non_unique,
dominant_index_name,
dominant_index_columns,
dominant_index_non_unique,
subpart_exists,
sql_drop_index
) AS
SELECT
redundant_keys.table_schema,
redundant_keys.table_name,
redundant_keys.index_name AS redundant_index_name,
redundant_keys.index_columns AS redundant_index_columns,
redundant_keys.non_unique AS redundant_index_non_unique,
dominant_keys.index_name AS dominant_index_name,
dominant_keys.index_columns AS dominant_index_columns,
dominant_keys.non_unique AS dominant_index_non_unique,
IF(redundant_keys.subpart_exists OR dominant_keys.subpart_exists, 1 ,0) AS subpart_exists,
CONCAT(
'ALTER TABLE `', redundant_keys.table_schema, '`.`', redundant_keys.table_name, '` DROP INDEX `', redundant_keys.index_name, '`'
) AS sql_drop_index
FROM
x$schema_flattened_keys AS redundant_keys
INNER JOIN x$schema_flattened_keys AS dominant_keys
USING (TABLE_SCHEMA, TABLE_NAME)
WHERE
redundant_keys.index_name != dominant_keys.index_name
AND (
(
/* Identical columns */
(redundant_keys.index_columns = dominant_keys.index_columns)
AND (
(redundant_keys.non_unique > dominant_keys.non_unique)
OR (redundant_keys.non_unique = dominant_keys.non_unique
AND IF(redundant_keys.index_name='PRIMARY', '', redundant_keys.index_name) > IF(dominant_keys.index_name='PRIMARY', '', dominant_keys.index_name)
)
)
)
OR
(
/* Non-unique prefix columns */
LOCATE(CONCAT(redundant_keys.index_columns, ','), dominant_keys.index_columns) = 1
AND redundant_keys.non_unique = 1
)
OR
(
/* Unique prefix columns */
LOCATE(CONCAT(dominant_keys.index_columns, ','), redundant_keys.index_columns) = 1
AND dominant_keys.non_unique = 0
)
);
|
