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
|
--
-- View: x$schema_flattened_keys
--
-- Helper view for the schema_redundant_keys view.
--
-- mysql> select * from sys.x$schema_flattened_keys;
-- +---------------+---------------------+------------------------------+------------+----------------+-----------------+
-- | table_schema | table_name | index_name | non_unique | subpart_exists | index_columns |
-- +---------------+---------------------+------------------------------+------------+----------------+-----------------+
-- | mem__advisors | advisor_initialized | PRIMARY | 0 | 0 | advisorClassId |
-- | mem__advisors | advisor_schedules | advisorClassIdIdx | 1 | 0 | advisorClassId |
-- | mem__advisors | advisor_schedules | PRIMARY | 0 | 0 | schedule_id |
-- | mem__advisors | app_identity_path | FK_7xbq2i81hgo0xlvnb6rr77s21 | 1 | 0 | for_schedule_id |
-- | mem__advisors | app_identity_path | PRIMARY | 0 | 0 | hib_id |
-- ...
--
CREATE OR REPLACE
ALGORITHM = TEMPTABLE
DEFINER = 'mariadb.sys'@'localhost'
SQL SECURITY INVOKER
VIEW x$schema_flattened_keys (
table_schema,
table_name,
index_name,
non_unique,
subpart_exists,
index_columns
) AS
SELECT
TABLE_SCHEMA,
TABLE_NAME,
INDEX_NAME,
MAX(NON_UNIQUE) AS non_unique,
MAX(IF(SUB_PART IS NULL, 0, 1)) AS subpart_exists,
GROUP_CONCAT(COLUMN_NAME ORDER BY SEQ_IN_INDEX) AS index_columns
FROM INFORMATION_SCHEMA.STATISTICS
WHERE
INDEX_TYPE='BTREE'
AND TABLE_SCHEMA NOT IN ('mysql', 'sys', 'INFORMATION_SCHEMA', 'PERFORMANCE_SCHEMA')
GROUP BY
TABLE_SCHEMA, TABLE_NAME, INDEX_NAME;
|
