From 2b7629db67160aa7db1c8e7f6f6a61d76b4495b9 Mon Sep 17 00:00:00 2001 From: shlomi-noach Date: Sat, 1 Aug 2015 19:04:24 +0200 Subject: [PATCH 1/3] added schema_redundant_keys (depends on _schema_flattened_keys): cehcking for duplicate indexes --- views/i_s/_schema_flattened_keys.sql | 24 +++++++++++++++++ views/i_s/schema_redundant_keys.sql | 52 ++++++++++++++++++++++++++++++++++++ 2 files changed, 76 insertions(+) create mode 100644 views/i_s/_schema_flattened_keys.sql create mode 100644 views/i_s/schema_redundant_keys.sql diff --git a/views/i_s/_schema_flattened_keys.sql b/views/i_s/_schema_flattened_keys.sql new file mode 100644 index 0000000..f33fa7f --- /dev/null +++ b/views/i_s/_schema_flattened_keys.sql @@ -0,0 +1,24 @@ +-- +-- Redundant indexes: indexes which are made redundant (or duplicate) by other (dominant) keys. +-- + +CREATE OR REPLACE + ALGORITHM = TEMPTABLE + DEFINER = 'root'@'localhost' + SQL SECURITY INVOKER +VIEW _schema_flattened_keys 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 +; + diff --git a/views/i_s/schema_redundant_keys.sql b/views/i_s/schema_redundant_keys.sql new file mode 100644 index 0000000..ae4b360 --- /dev/null +++ b/views/i_s/schema_redundant_keys.sql @@ -0,0 +1,52 @@ +-- +-- Redundant indexes: indexes which are made redundant (or duplicate) by other (dominant) keys. +-- +CREATE OR REPLACE + ALGORITHM = TEMPTABLE + DEFINER = 'root'@'localhost' + SQL SECURITY INVOKER +VIEW schema_redundant_keys 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 + _schema_flattened_keys AS redundant_keys + INNER JOIN _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 + ) + ) +; From 271b46d39be123f17d260aab3efbc64cf93eadf6 Mon Sep 17 00:00:00 2001 From: shlomi-noach Date: Sat, 1 Aug 2015 19:20:49 +0200 Subject: [PATCH 2/3] added schema_candidate_keys, schema_candidate_keys_recommended (both depends _schema_unique_keys) --- views/i_s/_schema_unique_keys.sql | 22 ++++++++++++ views/i_s/schema_candidate_keys.sql | 46 +++++++++++++++++++++++++ views/i_s/schema_candidate_keys_recommended.sql | 24 +++++++++++++ 3 files changed, 92 insertions(+) create mode 100644 views/i_s/_schema_unique_keys.sql create mode 100644 views/i_s/schema_candidate_keys.sql create mode 100644 views/i_s/schema_candidate_keys_recommended.sql diff --git a/views/i_s/_schema_unique_keys.sql b/views/i_s/_schema_unique_keys.sql new file mode 100644 index 0000000..f21ea47 --- /dev/null +++ b/views/i_s/_schema_unique_keys.sql @@ -0,0 +1,22 @@ +-- +-- Unique keys: listing of all unique keys aith aggregated column names and additional data +-- +CREATE OR REPLACE + ALGORITHM = TEMPTABLE + DEFINER = 'root'@'localhost' + SQL SECURITY INVOKER +VIEW _schema_unique_keys AS + SELECT + TABLE_SCHEMA, + TABLE_NAME, + INDEX_NAME, + COUNT(*) AS COUNT_COLUMN_IN_INDEX, + IF(SUM(NULLABLE = 'YES') > 0, 1, 0) AS has_nullable, + IF(INDEX_NAME = 'PRIMARY', 1, 0) AS is_primary, + GROUP_CONCAT(COLUMN_NAME ORDER BY SEQ_IN_INDEX ASC) AS COLUMN_NAMES, + SUBSTRING_INDEX(GROUP_CONCAT(COLUMN_NAME ORDER BY SEQ_IN_INDEX ASC), ',', 1) AS FIRST_COLUMN_NAME + FROM INFORMATION_SCHEMA.STATISTICS + WHERE NON_UNIQUE=0 + GROUP BY TABLE_SCHEMA, TABLE_NAME, INDEX_NAME +; + diff --git a/views/i_s/schema_candidate_keys.sql b/views/i_s/schema_candidate_keys.sql new file mode 100644 index 0000000..0e43399 --- /dev/null +++ b/views/i_s/schema_candidate_keys.sql @@ -0,0 +1,46 @@ +-- +-- Candidate keys: listing of prioritized candidate keys: keys which are UNIQUE, by order of best-use. +-- + +CREATE OR REPLACE + ALGORITHM = TEMPTABLE + DEFINER = 'root'@'localhost' + SQL SECURITY INVOKER +VIEW schema_candidate_keys AS +SELECT + COLUMNS.TABLE_SCHEMA AS table_schema, + COLUMNS.TABLE_NAME AS table_name, + _schema_unique_keys.INDEX_NAME AS index_name, + _schema_unique_keys.has_nullable AS has_nullable, + _schema_unique_keys.is_primary AS is_primary, + _schema_unique_keys.COLUMN_NAMES AS column_names, + _schema_unique_keys.COUNT_COLUMN_IN_INDEX AS count_column_in_index, + COLUMNS.DATA_TYPE AS data_type, + COLUMNS.CHARACTER_SET_NAME AS character_set_name, + (CASE IFNULL(CHARACTER_SET_NAME, '') + WHEN '' THEN 0 + ELSE 1 + END << 20 + ) + + (CASE LOWER(DATA_TYPE) + WHEN 'tinyint' THEN 0 + WHEN 'smallint' THEN 1 + WHEN 'int' THEN 2 + WHEN 'timestamp' THEN 3 + WHEN 'bigint' THEN 4 + WHEN 'datetime' THEN 5 + ELSE 9 + END << 16 + ) + (COUNT_COLUMN_IN_INDEX << 0 + ) AS candidate_key_rank_in_table +FROM + INFORMATION_SCHEMA.COLUMNS + INNER JOIN _schema_unique_keys ON ( + COLUMNS.TABLE_SCHEMA = _schema_unique_keys.TABLE_SCHEMA AND + COLUMNS.TABLE_NAME = _schema_unique_keys.TABLE_NAME AND + COLUMNS.COLUMN_NAME = _schema_unique_keys.FIRST_COLUMN_NAME + ) +ORDER BY + COLUMNS.TABLE_SCHEMA, COLUMNS.TABLE_NAME, candidate_key_rank_in_table +; + diff --git a/views/i_s/schema_candidate_keys_recommended.sql b/views/i_s/schema_candidate_keys_recommended.sql new file mode 100644 index 0000000..66ec779 --- /dev/null +++ b/views/i_s/schema_candidate_keys_recommended.sql @@ -0,0 +1,24 @@ +-- +-- Candidate keys: listing of prioritized candidate keys: keys which are UNIQUE, by order of best-use. +-- + +CREATE OR REPLACE + ALGORITHM = TEMPTABLE + DEFINER = 'root'@'localhost' + SQL SECURITY INVOKER +VIEW schema_candidate_keys_recommended AS +SELECT + table_schema, + table_name, + SUBSTRING_INDEX(GROUP_CONCAT(index_name ORDER BY candidate_key_rank_in_table ASC), ',', 1) AS recommended_index_name, + CAST(SUBSTRING_INDEX(GROUP_CONCAT(has_nullable ORDER BY candidate_key_rank_in_table ASC), ',', 1) AS UNSIGNED INTEGER) AS has_nullable, + CAST(SUBSTRING_INDEX(GROUP_CONCAT(is_primary ORDER BY candidate_key_rank_in_table ASC), ',', 1) AS UNSIGNED INTEGER) AS is_primary, + CAST(SUBSTRING_INDEX(GROUP_CONCAT(count_column_in_index ORDER BY candidate_key_rank_in_table ASC), ',', 1) AS UNSIGNED INTEGER) AS count_column_in_index, + SUBSTRING_INDEX(GROUP_CONCAT(column_names ORDER BY candidate_key_rank_in_table ASC SEPARATOR ' '), ' ', 1) AS column_names +FROM + schema_candidate_keys +GROUP BY + table_schema, table_name +ORDER BY + table_schema, table_name +; From b1ec8aed33b6899c5f20c2d80be518adcf56cab6 Mon Sep 17 00:00:00 2001 From: shlomi-noach Date: Sat, 1 Aug 2015 19:23:06 +0200 Subject: [PATCH 3/3] Revert "added schema_redundant_keys (depends on _schema_flattened_keys): cehcking for duplicate indexes" This reverts commit 2b7629db67160aa7db1c8e7f6f6a61d76b4495b9. --- views/i_s/_schema_flattened_keys.sql | 24 ----------------- views/i_s/schema_redundant_keys.sql | 52 ------------------------------------ 2 files changed, 76 deletions(-) delete mode 100644 views/i_s/_schema_flattened_keys.sql delete mode 100644 views/i_s/schema_redundant_keys.sql diff --git a/views/i_s/_schema_flattened_keys.sql b/views/i_s/_schema_flattened_keys.sql deleted file mode 100644 index f33fa7f..0000000 --- a/views/i_s/_schema_flattened_keys.sql +++ /dev/null @@ -1,24 +0,0 @@ --- --- Redundant indexes: indexes which are made redundant (or duplicate) by other (dominant) keys. --- - -CREATE OR REPLACE - ALGORITHM = TEMPTABLE - DEFINER = 'root'@'localhost' - SQL SECURITY INVOKER -VIEW _schema_flattened_keys 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 -; - diff --git a/views/i_s/schema_redundant_keys.sql b/views/i_s/schema_redundant_keys.sql deleted file mode 100644 index ae4b360..0000000 --- a/views/i_s/schema_redundant_keys.sql +++ /dev/null @@ -1,52 +0,0 @@ --- --- Redundant indexes: indexes which are made redundant (or duplicate) by other (dominant) keys. --- -CREATE OR REPLACE - ALGORITHM = TEMPTABLE - DEFINER = 'root'@'localhost' - SQL SECURITY INVOKER -VIEW schema_redundant_keys 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 - _schema_flattened_keys AS redundant_keys - INNER JOIN _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 - ) - ) -;