Skip to Main Content
MySQL® Administrator's Bible
book

MySQL® Administrator's Bible

by Sheeri K. Cabral, Keith Murphy
May 2009
Intermediate to advanced content levelIntermediate to advanced
888 pages
20h 33m
English
Wiley
Content preview from MySQL® Administrator's Bible

Chapter 6. MySQL Index Types

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 ...

Become an O’Reilly member and get unlimited access to this title plus top books and audiobooks from O’Reilly and nearly 200 top publishers, thousands of courses curated by job role, 150+ live events each month,
and much more.
Start your free trial

You might also like

MySQL® Administrator's Guide

MySQL® Administrator's Guide

MySQL AB
Microsoft® SQL Server 2005 Management and Administration

Microsoft® SQL Server 2005 Management and Administration

MCDBA Ross Mistry, MCSE Chris Amaris, MCSE Alec Minty

Publisher Resources

ISBN: 9780470416914Purchase book