You should look at the CREATE TABLE syntax for its full range of syntactical options, because you will
concentrate on the ALTER TABLE syntax for your examples.Now you'll look a little closer at MySQL's support for the various types of keys.Single-Column Keys
The basic syntax to create a key on a single column is as follows:
ALTER TABLE table_name ADD KEY index_name (column_name[,...])
To create a key on the customer state column, as in Figure 11.3, the syntax would be
ALTER TABLE customers ADD KEY lastn_idx (lastname);or
ALTER TABLE customers ADD INDEX lastn_idx (lastname);If you now type
DESC customers;you can view the table description:
mysql> DESC customers;
+-----------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------+--------------+------+-----+---------+-------+
| lastname | varchar(30) | | MUL | | |
| firstname | varchar(30) | | | | |
| address | varchar(100) | | | | |
| state | varchar(30) | | | | |
| country | varchar(30) | | | | |
+-----------+--------------+------+-----+---------+-------+
5 rows in set (0.00 sec)
You can see that something has appeared in the Key entry for lastname. You have created a single-
column index!
The MUL in the Key column tells you that it is a non-unique key. If you now want to check what keys
exist, type the following:
SHOW KEYS FROM customers;or
SHOW INDEX FROM customers;You will see that you have the following:
+---------+----------+---------+------------+-----------+---------+-----------+--------+
| Table |Non_unique| Key_name|Seq_in_index|Column_name|Collation|Cardinality|Sub_part|
+---------+----------+---------+------------+-----------+---------+-----------+--------+
|customers| 1 |lastn_idx| 1 | lastname | A | NULL | NULL |
+---------+----------+---------+------------+-----------+---------+-----------+--------+
1 row in set (0.01 sec)To drop the index, enter the following:
ALTER TABLE customers DROP KEY lastn_idx;which is the same as
ALTER TABLE customers DROP INDEX lastn_idx;