MySQL® Administrator's Bible

Chapter 6. MySQL Index Types

IN THIS CHAPTER

• Looking at keys and indexes

• Using indexes to speed up lookups

• Creating and dropping key constraints

• Using FULLTEXT indexes

In SQL theory, a key is a data constraint, such as a unique key or foreign key. On the other hand, an index is an implementation detail, provided to be able access a limited set of data more quickly. MySQL has keys that act as data constraints, and indexes that make a small amount of table data readily accessible in a certain order. MySQL allows key constraints and indexes to be applied to a single data field or to more than one data field. A key constraint or index applied to one data field is a simple key constraint or index; on more than one data field is a composite key constraint or index.

Looking at Keys and Indexes

Unique key constraints in MySQL (UNIQUE KEY and PRIMARY KEY) limit the data in a table by allowing only one set of values for the indexed data. A foreign key constraint (FOREIGN KEY) limits the data in a table by requiring that the set of values for the indexed data match data from outside the table.

Regular indexes (INDEX, FULLTEXT INDEX, SPATIAL INDEX) and unique indexes (UNIQUE KEY and PRIMARY KEY) create an object separate from a table, with its own data structure, using data from the table. This allows looking up those values to be simpler.

Because UNIQUE KEY and PRIMARY KEY function as both keys and indexes, the term key is sometimes used interchangeably with the term index. We use the term key when ...