candidate_keys_recommended
NAME
candidate_keys_recommended: Recommended candidate key per table.TYPE
ViewDESCRIPTION
candidate_keys_recommended recommends a single candidate key per table, where such keys are available.
Follow discussion on candidate_keys for more on candidate keys.
This view assist in deciding whether assigned PRIMARY KEYs for tables are indeed the best candidate keys assigned. With InnoDB, where a PRIMARY KEY is of greater significance than secondary keys, it is important to choose the PRIMARY KEY wisely. Other candidate keys can be promoted to PRIMARY in place of a weak PRIMARY KEY.
The common utilization of this view would be to note down recommended keys which are
STRUCTURE
mysql> DESC common_schema.candidate_keys_recommended; +------------------------+---------------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +------------------------+---------------------+------+-----+---------+-------+ | table_schema | varchar(64) | NO | | | | | table_name | varchar(64) | NO | | | | | recommended_index_name | longtext | YES | | NULL | | | has_nullable | bigint(67) unsigned | YES | | NULL | | | is_primary | bigint(67) unsigned | YES | | NULL | | | column_names | longtext | YES | | NULL | | +------------------------+---------------------+------+-----+---------+-------+
SYNOPSIS
Columns of this view:
- table_schema: schema of candidate key
- table_name: table of candidate key
- recommended_index_name: name of recommended candidate key
- has_nullable: 1 if any column in recommended index is NULLable; 0 if all columns are NOT NULL
- is_primary: 1 if recommended key is PRIMARY, 0 otherwise.
- column_names: names of columns covered by key
EXAMPLES
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 | +--------------+---------------+------------------------+--------------+------------+--------------+
In the above we note that for all tables the recommended candidate key is indeed the PRIMARY KEY.