Indices (MySQL & mSQL)

6.4. Indices

While MySQL and mSQL both have greater 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. As we discussed in Chapter 2, "Database Design", most database engines enable you to help it in these searches through a tool called an index.

Indices 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 indices is to create an index for columns on which you tend to search the most. MySQL and mSQL support a common syntax for index creation:

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 materials (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 indices 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. All fields declared in a unique index must be declared as being 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 indices within the "main" index. For example, name by itself and name and rank together are both "free" indices created 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 which will be supporting joins are good candidates for primary keys. See Chapter 2, "Database Design" for a detailed discussion on how to design your tables with good primary keys.