Multiple-Column Keys
You can create a multiple-column key, also called a composite index, on more than one column in the same
table. For example
ALTER TABLE customers ADD KEY comp_index (lastname, state, country);When constructing the key, MySQL works from the left across the columns specified, creating subsets
of the column to include in the key. Thus, the following sets of columns would be indexed:
(lastname, state, country)
(lastname, state)
(lastname)You can ask MySQL to display the composite index:
mysql> SHOW KEYS FROM customers;
+---------+----------+----------+------------+-----------+---------+-----------+--------+
| Table |Non_unique| Key_name |Seq_in_index|Column_name|Collation|Cardinality|Sub_part|
+---------+----------+----------+------------+-----------+---------+-----------+--------+
|customers| 1 |comp_index| 1 | lastnam | A | NULL| NULL|
|customers| 1 |comp_index| 2 | state | A | NULL| NULL|
|customers| 1 |comp_index| 3 | country | A | NULL| NULL|
+---------+----------+----------+------------+-----------+---------+-----------+--------+
3 rows in set (0.00 sec)
When creating multiple-column keys, it's good practice to place the most restrictive column first (in the
previous example, lastname is more restrictive than state, which is more restrictive than country).
This will help database performance when doing a read operation.
Compiling a WHERE clause using the field or fields at the left side of the composite key is most efficient,
because it will ensure that MySQL uses the key in its lookup.To drop the composite key, the procedure is the same:
ALTER TABLE customers DROP KEY comp_index;Multiple-column keys present a greater database overhead than single-column keys. Bear this in mind
when deciding whether to use a multiple- or single-column key.Partial Keys
When creating a key on a CHAR or VARCHAR type column, it is possible to index the first few characters of
the column. You reference the first part, or prefix, of a column by appending (length) to the name of the
column.
For example, you may want to create a key on the first 6 characters of a customer's name:
ALTER TABLE customers ADD KEY lastn_idx (lastname(6))
If you were doing this at the time of creating the table, you would specify (showing only syntax related to
(lastname)):
CREATE TABLE customers (lastname VARCHAR(30) NOT NULL, KEY lastname_idx (lastname(6)),...)If you enter the following
mysql> SHOW KEYS FROM customers;you would get the following output:
+---------+----------+-----------+------ -----+-----------+---------+-----------+--------+
| Table |Non_unique| Key_name |Seq_in_index|Column_name|Collation|Cardinality|Sub_part|
+---------+----------+-----------+------------+-----------+---------+-----------+--------+
|customers| 1 |lastn_index| 1 | lastname | A | NULL | 6|
+---------+----------+-----------+------------+-----------+---------+-----------+--------+