Adding, Removing, and Changing Indexes

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 anew 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 anew 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 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) 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 fora 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 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

Download 4.24 Mb.

Share with your friends: