-> artist_id SMALLINT(5) NOT NULL DEFAULT 0

You can see we’ve used the keyword
KEY
to tell MySQL that we want an extra index;
you can use the word
INDEX
in place of KEY. Following this, we’ve named the index—
in this example, we’ve named it after the column name—and then we’ve included the column to index in parentheses. You can also add indexes after tables are created—in fact, you can pretty much change anything about a table after its creation—and this is discussed in Altering Structures.”
You can build an index on more than one column. For example, consider the following customer table:
mysql> CREATE TABLE customer (
-> cust_id INT) NOT NULL DEFAULT 0,
-> firstname CHAR(50),
-> secondname CHAR(50),
-> surname CHAR(50),
-> PRIMARY KEY (cust_id),
-> KEY names (firstname, secondname, surname));
Query OK, 0 rows affected (0.01 sec)
You can see that we’ve added a primary key index on the cust_id identifier column,
and we’ve also added another index—called names
—that includes the firstname
,
secondname
, and surname columns in this order. Let’s now consider how you can use that extra index.
You can use the names index for fast searching by combinations of the three name columns. For example, it’s useful in the following query:
mysql> SELECT * FROM customer WHERE
-> firstname = "Rose" AND
-> secondname = "Elizabeth" AND
-> surname = "Williams";
We know it helps the search, because all columns listed in the index are used in the query. You can use the
EXPLAIN
statement to check whether what you think should happen is in fact happening:
mysql> EXPLAIN SELECT * FROM customer WHERE

Download 4.24 Mb.

Share with your friends: