That's where a key, or index, can help. A key is a field in the table that helps you locate entries in a
table in a far more efficient way.How Keys Work
A key exists like an extra table in the database, albeit belonging to its parent table. It takes up physical space
on the hard disk (or other storage areas) of the database. It can be as big as the main table and,
theoretically, even bigger.
You define your key to relate to one or a number of columns in a specific table. Because the data in a
key is totally derived from the table, you can drop and re-create a key without any loss of data. As you
will see, there may be good reasons for doing this.
Suppose that you wanted to select by the state line of each customer's address. You might create a key
as shown in Figure 11.3. After you have the state key, you can easily locate all customers living in
California, for example. You could just as easily sort customers by state rather than by name.Figure 11.3 Using a key to access the state column of a Customers table.
Benefits of Using a Key
Proper use of keys can significantly improve database performance. To use the analogy of a book index,
consider how few pages it takes in the index of a book to give you a fast way of searching for the important
themes. Compare that to how long it would take if you were scanning through the volume page-by-page.
Keys in modern databases are designed to minimize the amount of disk accessing needed when
reading from them. Even the method of scanning through the key and determining whether the data
matches what you're looking for comprises sophisticated matching algorithms.MySQL's Key Support
MySQL supports the following commands for creating keys on existing tables:
ALTER TABLE table_name ADD (KEY|INDEX) index_name (column_name[,...]);
ALTER TABLE table_name ADD UNIQUE index_name (column_name[,...]);
ALTER TABLE table_name ADD PRIMARY KEY index_name (column_name[,...]);Note that in MySQL, key and index are synonymous.These are the preferred formats for adding keys to existing tables. For compatibility with other
implementations of SQL, MySQL also supports the following:
CREATE INDEX index_name ON table_name (column_name[,...]);
CREATE UNIQUE INDEX [index_name] ON table_name (column_name[,...]);
CREATE PRIMARY KEY ON table_name (column_name,...);You can define keys when you create a table. The following is an example of defining a key:
CREATE TABLE table_name (column_name field_type [NULL|NOT NULL],KEY
col_index(column_name));For a primary key (with more options shown), you would use the following syntax:
CREATE TABLE table_name (column_name [NULL|NOT NULL][DEFAULT default_value]
[AUTO_INCREMENT] [PRIMARY KEY] [reference definition]...);