schema_analysis_views
SYNOPSIS
Schema analysis views: a collection of views, analyzing schema design, listing design errors, generating SQL statements based on schema design
- candidate_keys: Listing of prioritized candidate keys: keys which are UNIQUE, by order of best-use.
- candidate_keys_recommended: Recommended candidate key per table.
- no_pk_innodb_tables: List InnoDB tables where no PRIMARY KEY is defined
- redundant_keys: List indexes which are made redundant (or duplicate) by other (dominant) keys.
- routines: Complement INFORMATION_SCHEMA.ROUTINES missing info.
- sql_alter_table: Generate ALTER TABLE SQL statements per table, with engine and create options
- sql_foreign_keys: Generate create/drop foreign key constraints SQL statements
- sql_range_partitions: Generate SQL statements for managing range partitions
- table_charset: List tables, their character sets and collations
- text_columns: List textual columns character sets & collations
DESCRIPTION
Views in this category perform various schema analysis operations or offer SQL generation code cased on schema analysis.
EXAMPLES
Detect duplicate keys on sakila.actor:
mysql> ALTER TABLE `sakila`.`actor` ADD INDEX `actor_id_idx` (`actor_id`);
mysql> ALTER TABLE `sakila`.`actor` ADD INDEX `last_and_first_names_idx` (`last_name`, `first_name`);
mysql> ALTER TABLE `sakila`.`film_actor` ADD UNIQUE KEY `film_and_actor_ids_idx` (`film_id`, `actor_id`);
mysql> SELECT * FROM common_schema.redundant_keys \G
*************************** 1. row ***************************
table_schema: sakila
table_name: actor
redundant_index_name: idx_actor_last_name
redundant_index_columns: last_name
redundant_index_non_unique: 1
dominant_index_name: last_and_first_names_idx
dominant_index_columns: last_name,first_name
dominant_index_non_unique: 1
subpart_exists: 0
sql_drop_index: ALTER TABLE `sakila`.`actor` DROP INDEX `idx_actor_last_name`
*************************** 2. row ***************************
table_schema: sakila
table_name: actor
redundant_index_name: actor_id_idx
redundant_index_columns: actor_id
redundant_index_non_unique: 1
dominant_index_name: PRIMARY
dominant_index_columns: actor_id
dominant_index_non_unique: 0
subpart_exists: 0
sql_drop_index: ALTER TABLE `sakila`.`actor` DROP INDEX `actor_id_idx`
*************************** 3. row ***************************
table_schema: sakila
table_name: film_actor
redundant_index_name: idx_fk_film_id
redundant_index_columns: film_id
redundant_index_non_unique: 1
dominant_index_name: film_and_actor_ids_idx
dominant_index_columns: film_id,actor_id
dominant_index_non_unique: 0
subpart_exists: 0
sql_drop_index: ALTER TABLE `sakila`.`film_actor` DROP INDEX `idx_fk_film_id`
Show recommended candidate keys for tables in sakila
mysql> SELECT * FROM common_schema.candidate_keys_recommended WHERE TABLE_SCHEMA='sakila'; +--------------+---------------+------------------------+--------------+------------+--------------+ | table_schema | table_name | recommended_index_name | has_nullable | is_primary | column_names | +--------------+---------------+------------------------+--------------+------------+--------------+ | sakila | actor | PRIMARY | 0 | 1 | actor_id | | sakila | address | PRIMARY | 0 | 1 | address_id | | sakila | category | PRIMARY | 0 | 1 | category_id | | sakila | city | PRIMARY | 0 | 1 | city_id | | sakila | country | PRIMARY | 0 | 1 | country_id | | sakila | customer | PRIMARY | 0 | 1 | customer_id | | sakila | film | PRIMARY | 0 | 1 | film_id | | sakila | film_actor | PRIMARY | 0 | 1 | actor_id | | sakila | film_category | PRIMARY | 0 | 1 | film_id | | sakila | film_text | PRIMARY | 0 | 1 | film_id | | sakila | inventory | PRIMARY | 0 | 1 | inventory_id | | sakila | language | PRIMARY | 0 | 1 | language_id | | sakila | payment | PRIMARY | 0 | 1 | payment_id | | sakila | rental | PRIMARY | 0 | 1 | rental_id | | sakila | staff | PRIMARY | 0 | 1 | staff_id | | sakila | store | PRIMARY | 0 | 1 | store_id | +--------------+---------------+------------------------+--------------+------------+--------------+