3.5 Indexing

While MySQL has better performance than any of the larger database servers, some problems still call for careful database design. For instance, if we had a table with millions of rows of data, a search for a specific row would take a long time. Most database engines allow indexes to aid in such searches.

Indexes help the database store data in a way that makes for quicker searches. Unfortunately, you sacrifice disk space and modification speed for the benefit of quicker searches. The most efficient use of indexes is to create an index for columns on which you tend to search the most. MySQL supports the following syntax for creating an index for a table:

CREATE INDEX index_name ON tablename (column1,
                                      column2,
                                      ...,
                                      columnN)

MySQL also lets you create an index at the same time you create a table using the following syntax:

CREATE TABLE material (id         INT      NOT NULL,
                       name       CHAR(50) NOT NULL,
                       resistance INT,
                       melting_pt REAL,
                       INDEX index1 (id, name),
                       UNIQUE INDEX index2 (name))

The previous example creates two indexes for the table. The first index—named index1—consists of both the id and name fields. The second index includes only the name field and specifies that values for the name field must always be unique. If you try to insert a field with a name held by a row already in the database, the insert will fail. Generally, you should declare all fields in a unique index as NOT NULL.

Even though we created an index for name by itself, we did not create an index for just id. If we did want such an index, we would not need to create it—it is already there. When an index contains more than one column (for example: name, rank, and serial_number), MySQL reads the columns in order from left to right. Because of the structure of the index MySQL uses, any subset of the columns from left to right are automatically created as indexes within the "main" index. For example, name by itself and name and rank together are both indexes created "for free" when you create the index name, rank, serial_number. An index of rank by itself or name and serial_number together, however, is not created unless you explicitly create it yourself.

MySQL also supports the ANSI SQL semantics of a special index called a primary key. In MySQL, a primary key is a unique key with the name PRIMARY. By calling a column a primary key at creation, you are naming it as a unique index that will support table joins. The following example creates a cities table with a primary key of id:

CREATE TABLE cities (id      INT  NOT NULL PRIMARY KEY,
                     name    VARCHAR(100),
                     pop     MEDIUMINT,
                     founded DATE)

Before you create a table, you should determine which fields, if any, should be keys. As we mentioned above, any fields that will support joins are good candidates for primary keys. See Chapter 7 for a detailed discussion on how to design your tables with good primary keys.

ANSI SQL supports a special kind of key called a foreign key. Foreign keys help protect database integrity by enabling the database to manage things such as the deletion of rows with dependent relationships in other tables. Though MySQL supports the ANSI syntax for foreign keys, it does not actually use them to perform integrity checking in the database. This is a situation in which the introduction of a feature would cause a slowdown in performance with little real benefit. Applications themselves should generally worry about foreign key integrity.