Altering Tables - Learning MySQL and MariaDB - page 97

EXPLAIN SELECT * FROM birdwatchers.humans
WHERE name_last = 'Hollar' \G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: humans
type: ref
possible_keys: human_names
key: human_names
key_len: 28
ref: const
rows: 1
Extra: Using where
As shown in the results, this time the possible_keys field indicates that the hu-
man_names key could be used. If there were more than one possible key that could be
used, the line would list them here. In line with the index's presence in pos-
sible_keys , the key shows that the human_names index was actually used. Basic-
ally, when a SELECT is run in which the user wants to search the table based on the per-
son's last name, MySQL will use the human_names index that we created, and not
search the name_last column sequentially. That's what we want. That will make for a
quicker search.
Now that you hopefully have a better understanding of indexes in general and their rela-
tion to columns, let's go back to the earlier task of renaming the column in the conser-
vation_status table from status_id to conservation_status_id . Because
the index is associated with the column, we need to remove that association in the index.
Otherwise, the index will be associated with a column that does not exist from its per-
spective: it will be looking for the column by the old name. So, let's delete the index and
rename the column, and then add a new index based on the new column name. To do that,
enter the following SQL statement in mysql :
ALTER TABLE conservation_status
DROP PRIMARY KEY ,
CHANGE status_id conservation_status_id INT PRIMARY KEY
AUTO_INCREMENT ;
The clauses must be inthe order shown, because the index must be dropped before the
column with which it's associated can be renamed. Don't worry about losing data: the
data in the columns is not deleted, only the index, which will be re-created easily by
MySQL. We don't have to give the name of the associated column when dropping a
PRIMARY KEY . There is and can be only one primary key.