Managing & Using MySQL, 2nd Edition

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