When you create a table, consider whether it should have indexes, because they have important benefits:

• Indexes contain sorted values. This allows MySQL to find rows containing particular values faster. The effect can be particularly dramatic for joins, which have the potential to require many combinations of rows to be examined.

• Indexes result in less disk I/O. The server can use an index to go directly to the relevant table records, which reduces the number of records it needs to read. Furthermore, if a query displays information only from indexed columns, MySQL might be able to process it by reading only the indexes and without accessing data rows at all.

MySQL supports four types of indexes:

• A nonunique index is one in which any key value may occur multiple times. This type of index is defined with the keyword INDEX or KEY.

• A UNIQUE index is unique-valued; that is, every key value is required to be different than all others. (The exception is that NULL values may occur multiple times.)

• A PRIMARY KEY also is a unique-valued index. It is similar to a UNIQUE index, but has additional restrictions:

  • A table may have multiple UNIQUE indexes, but at most one PRIMARY KEY.

  • A UNIQUE index can contain NULL values, whereas a PRIMARY KEY cannot.

• A FULLTEXT index is specially designed for text searching.

To define indexes when you're initially creating a table, use CREATE TABLE. To add indexes to an already existing table, use ALTER TABLE or CREATE INDEX. To drop indexes, use ALTER TABLE or DROP INDEX.

ALTER TABLE can add or drop several indexes in the same statement, which is faster than processing each one separately. CREATE INDEX and DROP INDEX allow only one index to be added or dropped at a time.

Index creation using the INDEX, UNIQUE, and PRIMARY KEY keywords is discussed in the "Core Study Guide." FULLTEXT indexes are not covered there because they are a more specialized kind of index. Instead, FULLTEXT indexing is discussed in section 13.1.4, "FULLTEXT Indexes."