candidate_keys
NAME
candidate_keys: Listing of prioritized candidate keys: keys which are UNIQUE, by order of best-use.TYPE
ViewDESCRIPTION
candidate_keys lists candidate keys for all tables. Each candidate key gets a score: a lower score is given to "better" keys.
Candidate keys are, technically, simply UNIQUE KEYs. Conceptually, these are keys whose values are able to represent tuples (rows). Such keys can be used by different operations on a table, such as separating it to chunks, breaking long, heavy weight operations into smaller, faster operations.
MySQL's PRIMARY KEYs must not cover NULLable columns. This view indicates whether a key has NULLable columns. While technically this means the key is not an immediate candidate (trying to turn it into PRIMARY KEY will fail due to NULLable columns), such keys are nevertheless listed, as often times columns are created NULLable by mistake; that is: many times a NULLable column never has NULL values. In such cases, an ALTER TABLE MODIFY COLUMN is required so as to make the key a true candidate key.
Not all candidate keys are the same. Some are "better" than others in terms of space and I/O. A UNIQUE KEY over a couple of INTEGER columns is smaller (hence "better") than a UNIQUE KEY over a VARCHAR(192) field (e.g. some URL). It is easier to walk through the table using smaller keys, since less search is involved.
candidate_keys provides with a heuristic ranking of candidate keys within a table. Each candidate key receives a candidate_key_rank_in_table rank (score). The smaller the better; so "better" keys can be detected using ORDER BY.
The heuristic works as follows:
- Non-character-typed columns get better score than character-typed columns. Only first column in index is compared in this heuristic.
- Smaller data types get better score than larger data types (e.g. INT is smaller than DATETIME). Only first column in index is compared in this heuristic.
- Keys covering fewer columns get better score
- There is no preference for PRIMARY KEYs, although with InnoDB they are technically fastest in access to row data due to the clustering index structure of InnoDB tables. Consult the is_primary column to prefer PRIMARY KEYs.
STRUCTURE
mysql> DESC common_schema.candidate_keys; +-----------------------------+---------------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-----------------------------+---------------------+------+-----+---------+-------+ | table_schema | varchar(64) | NO | | | | | table_name | varchar(64) | NO | | | | | index_name | varchar(64) | NO | | | | | has_nullable | int(1) | NO | | 0 | | | is_primary | int(1) | NO | | 0 | | | column_names | longtext | YES | | NULL | | | count_column_in_index | bigint(21) | NO | | 0 | | | data_type | varchar(64) | NO | | | | | character_set_name | varchar(32) | YES | | NULL | | | candidate_key_rank_in_table | bigint(23) unsigned | YES | | NULL | | +-----------------------------+---------------------+------+-----+---------+-------+
SYNOPSIS
Columns of this view:
- table_schema: schema of candidate key
- table_name: table of candidate key
- index_name: name of candidate key
- has_nullable: 1 if any column in this index is NULLable; 0 if all columns are NOT NULL
- is_primary: 1 if this key is PRIMARY, 0 otherwise.
- column_names: names of columns covered by key
- count_column_in_index: number of columns covered by key
- data_type: data type of first column covered by key
- character_set_name: character set name of first column covered by key, or NULL if not character-typed
- candidate_key_rank_in_table: rank (score) of index within table. Lower is "better". It makes no sense to compare ranks between keys of different tables.
EXAMPLES
Show candidate key ranking for tables in sakila
mysql> SELECT * FROM common_schema.candidate_keys WHERE TABLE_SCHEMA='sakila'; +--------------+---------------+--------------------+--------------+------------+--------------------------------------+-----------------------+-----------+--------------------+-----------------------------+ | table_schema | table_name | index_name | has_nullable | is_primary | column_names | count_column_in_index | data_type | character_set_name | candidate_key_rank_in_table | +--------------+---------------+--------------------+--------------+------------+--------------------------------------+-----------------------+-----------+--------------------+-----------------------------+ | sakila | actor | PRIMARY | 0 | 1 | actor_id | 1 | smallint | NULL | 65537 | | sakila | address | PRIMARY | 0 | 1 | address_id | 1 | smallint | NULL | 65537 | | sakila | category | PRIMARY | 0 | 1 | category_id | 1 | tinyint | NULL | 1 | | sakila | city | PRIMARY | 0 | 1 | city_id | 1 | smallint | NULL | 65537 | | sakila | country | PRIMARY | 0 | 1 | country_id | 1 | smallint | NULL | 65537 | | sakila | customer | PRIMARY | 0 | 1 | customer_id | 1 | smallint | NULL | 65537 | | sakila | film | PRIMARY | 0 | 1 | film_id | 1 | smallint | NULL | 65537 | | sakila | film_actor | PRIMARY | 0 | 1 | actor_id,film_id | 2 | smallint | NULL | 65538 | | sakila | film_category | PRIMARY | 0 | 1 | film_id,category_id | 2 | smallint | NULL | 65538 | | sakila | film_text | PRIMARY | 0 | 1 | film_id | 1 | smallint | NULL | 65537 | | sakila | inventory | PRIMARY | 0 | 1 | inventory_id | 1 | mediumint | NULL | 589825 | | sakila | language | PRIMARY | 0 | 1 | language_id | 1 | tinyint | NULL | 1 | | sakila | payment | PRIMARY | 0 | 1 | payment_id | 1 | smallint | NULL | 65537 | | sakila | rental | PRIMARY | 0 | 1 | rental_id | 1 | int | NULL | 131073 | | sakila | rental | rental_date | 0 | 0 | rental_date,inventory_id,customer_id | 3 | datetime | NULL | 327683 | | sakila | staff | PRIMARY | 0 | 1 | staff_id | 1 | tinyint | NULL | 1 | | sakila | store | idx_unique_manager | 0 | 0 | manager_staff_id | 1 | tinyint | NULL | 1 | | sakila | store | PRIMARY | 0 | 1 | store_id | 1 | tinyint | NULL | 1 | +--------------+---------------+--------------------+--------------+------------+--------------------------------------+-----------------------+-----------+--------------------+-----------------------------+
In the above we can see tables film and store each have 2 possible candidate keys.