Databases Reference
In-Depth Information
able to add, alter, and remove indexes on the fly after your application is deployed.
This section shows you how. Modifying indexes does not affect the data stored in the
table.
We'll start with adding a new index. Imagine that the
artist
table is frequently queried
using a
WHERE
clause that specifies an
artist_name
. To speed this query, you've decided
to add a new index, which you've named
by_name
. Here's how you add it after the table
is created:
mysql>
ALTER TABLE artist ADD INDEX by_name (artist_name);
Query OK, 6 rows affected (0.02 sec)
Records: 6 Duplicates: 0 Warnings: 0
Again, you can use the terms
KEY
and
INDEX
interchangeably. You can check the results
with the
SHOW CREATE TABLE
statement:
mysql>
SHOW CREATE TABLE artist;
+--------+-----------------------------------------------+
| Table | Create Table |
+--------+-----------------------------------------------+
| artist | CREATE TABLE `artist` ( |
| | `artist_id` smallint(5) NOT NULL default '0', |
| | `artist_name` char(128) default NULL, |
| | PRIMARY KEY (`artist_id`), |
| | KEY `by_name` (`artist_name`) |
| | ) ENGINE=MyISAM DEFAULT CHARSET=latin1 |
+--------+-----------------------------------------------+
As expected, the new index forms part of the table structure. You can also specify a
primary key for a table after it's created:
mysql>
ALTER TABLE artist ADD PRIMARY KEY (artist_id);
Now let's consider how to remove an index. To remove a non-primary-key index, you
do the following:
mysql>
ALTER TABLE artist DROP INDEX by_name;
Query OK, 6 rows affected (0.01 sec)
Records: 6 Duplicates: 0 Warnings: 0
You can drop a primary-key index as follows:
mysql>
ALTER TABLE artist DROP PRIMARY KEY;
MySQL won't allow you to have multiple primary keys in a table. If you want to change
the primary key, you'll have to remove the existing index before adding the new one.
Consider this example:
mysql>
CREATE TABLE staff (staff_id INT, name CHAR(40));
Query OK, 0 rows affected (0.01 sec)
mysql>
ALTER TABLE staff ADD PRIMARY KEY (staff_id);
Query OK, 0 rows affected (0.00 sec)
Records: 0 Duplicates: 0 Warnings: 0
