Microsoft Word - Sam's Teach Yourself MySQL in 21 Days - SAMS.doc

1 row in set (0.00 sec)

Note For columns of the TEXT and BLOB data type, you have no option but to make a

partial index of the column.

Composite Partial Keys

You can create partial keys on multiple columns. For example

ALTER TABLE customers ADD KEY comp_index (lastname(6),state(2),country(3));

Now, suppose that the data in your table included the following:
mysql> SELECT * FROM customers;
+----------+-----------+---------------------------+--------+---------+
| lastname | firstname | address | state | country |
+----------+-----------+---------------------------+--------+---------+
| Shepherd | Tom | 33 Madison Drive, Oakland | CA | USA |
| Chapman | Frederick | 52 Ocean St, Sacramento | CA | USA |
| Lowe | Justin | 3 Hill Walk, Twin Creeks | WI | USA |
| Spokes | Chris | Red Fern House, Bradwell | Oxford | UK |
+----------+-----------+---------------------------+--------+---------+
4 rows in set (0.00 sec)
You can imagine your key would then hold data looking like Table 11.1.
Table 11.1 Composite Partial Keys

lastname state country comp_index

Shepherd CA USA ShephCAUSA^

Chapman CA USA

ChapmCAUSA

Lowe WI USA

Lowe WIUSA

Spokes Oxford UK SpokeOxUK^

When designing queries, you must remember that a query of the following form

SELECT * FROM customers WHERE lastname='Chapman';

would make use of the index when it is run. However, the following query

SELECT * FROM customers WHERE state='WI';

would not use the index. This is because indexes of (lastname, state, country), (lastname,

state), and (lastname) exist, but the second query tries to use only state; MySQL must find a

composite index with the required field leftmost to use it.

Much the same applies when using a partial key. If you have the following query

SELECT * FROM customers WHERE lastname LIKE 'Chap%';

it would use the key because it is looking for a lastname match by comparing the leftmost characters.

However, the following query

SELECT * FROM customers WHERE lastname LIKE '%hap%';

would not because it is looking for matches both before and after the given text.

Performance-wise, holding partial indexes will usually take up less space in the key (and hence the

disk) than having a key on the entire field. This saves space and may be just as effective; for example,

most names are fairly unique after six characters.