Databases Reference
In-Depth Information
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(4) 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
-> firstname = "Rose" AND
-> secondname = "Elizabeth" AND
-> surname = "Williams";
+----+-------------+----------+------+---------------+...
| id | select_type | table | type | possible_keys |...
+----+-------------+----------+------+---------------+...
| 1 | SIMPLE | customer | ref | names |...
+----+-------------+----------+------+---------------+...
...+-------+---------+-------------------+------+-------------+
...| key | key_len | ref | rows | Extra |
...+-------+---------+-------------------+------+-------------+
...| names | 153 | const,const,const | 1 | Using where |
...+-------+---------+-------------------+------+-------------+
1 row in set (0.00 sec)
