Sams Teach Yourself MySQL in 21 Days, Second Edition

Managing Indexes in MySQL

MySQL supports four kinds of indexes:

• PRIMARY KEY

• UNIQUE

• INDEX

• FULLTEXT

The first two allow only unique values to be used in the column to which they apply. Only one unique index can be designated the PRIMARY KEY, but several indexes can be designated UNIQUE. The third kind permits the reuse of the same value in that column and is denoted by the keyword INDEX.

The fourth index type, FULLTEXT, is intended for speeding up full-text searches within VARCHAR or TEXT type columns. It works with the MATCH function of a WHERE clause, as was explained in Day 8, “Querying Data.” FULLTEXT was added in MySQL version 3.23.23.

As you will see in a moment, you can apply an index on a single column of a table, or on multiple columns. With ...