12. Indexes

Sections
Creating an Index with CREATE INDEX
Dropping an Index with DROP INDEX

Recall from “Tables, Columns, and Rows” in Chapter 2 that rows stored in a table are unordered, as required by the relational model. This lack of order makes it easy for the DBMS to INSERT, UPDATE, and DELETE rows quickly, but its unfortunate side effect is that it makes searching and sorting inefficient. Suppose that you run this query:

SELECT *
  FROM authors
  WHERE au_lname = 'Hull';

To execute this query, the DBMS must search the entire table authors sequentially, comparing the value in each row’s au_lname column to the string Hull. Searching an entire table in a small database is trivial, but production database tables can have millions of rows.

DBMSs provide a mechanism called an index that has the same purpose as its book or library counterpart: speeding data retrieval. At a simplified level, an index is a sorted list in which every distinct value in an indexed column (or set of columns) is stored with the drive address (physical location) of the rows containing that value. Instead of reading an entire table to locate specific rows, the DBMS scans only the index for addresses to access directly. Indexed searches typically are orders of magnitude faster than sequential searches, but some tradeoffs are involved, as explained in this chapter.

Creating an Index with CREATE INDEX

Indexes are complex; their design and effects on performance depend on the idiosyncrasies of your DBMS’s optimizer. I’ll provide guidelines in this section, but search your DBMS documentation for index to learn how your DBMS implements and uses indexes. In general, indexes are appropriate for columns that are frequently:

In general, indexes are inappropriate for columns that:

When you’re creating an index, some important considerations are:

Indexes aren’t part of the SQL standard, so index-related SQL statements vary by DBMS, although the syntax for the minimal CREATE INDEX statement is the same for the DBMSs covered in this book.

To create an index:

Listing 12.1 creates a simple index named pub_id_idx on the column pub_id for the table titles. pub_id is a foreign key and is a good candidate for an index because:

Listing 12.1Create a simple index on the column pub_id for the table titles.

CREATE INDEX pub_id_idx
  ON titles (pub_id);

Listing 12.2 creates a simple unique index named title_name_idx on the column title_name for the table titles. The DBMS will create this index only if no duplicates already exist in the column title_name. This index also prohibits nondistinct title names from being INSERTed or UPDATEd in titles.

Listing 12.2Create a simple unique index on the column title_name for the table titles.

CREATE UNIQUE INDEX title_name_idx
  ON titles (title_name);

Listing 12.3 creates a composite index named state_city_idx on the columns state and city for the table authors. The DBMS uses this index when you sort rows in state plus city order. This index is useless for sorts and searches on state alone, city alone, or city plus state; you must create separate indexes for those purposes.

Listing 12.3Create a composite index on the columns state and city for the table authors.

CREATE INDEX state_city_idx
  ON authors (state, city);

Tips for CREATE INDEX

Dropping an Index with DROP INDEX

Use the DROP INDEX statement to destroy an index. Because an index is logically and physically independent of the data in its associated table, you can drop the index at any time without affecting the table (or other indexes). All SQL programs and other applications will continue to work if you drop an index, but access to previously indexed data will be slower.

The usual reasons for dropping an index are:

The SQL standard omits indexes, so index-related SQL statements vary by DBMS. This section describes how to drop an index for each DBMS covered in this book. If you’re using a different DBMS, then search the documentation for index to learn how to drop an index.

In Oracle, Db2, and PostgreSQL, index names must be unique within a database, so you don’t specify a table name when you drop an index. In Microsoft Access, Microsoft SQL Server, and MySQL, index names must be unique within a table but can be reused in other tables, so you must specify a table along with the index to be dropped. The examples in this section drop the index created by Listing 12.1 in the preceding section.

To drop an index in Microsoft Access or MySQL:

Listing 12.4aDrop the index pub_id_idx (Microsoft Access or MySQL).

DROP INDEX pub_id_idx
  ON titles;

To drop an index in Microsoft SQL Server:

Listing 12.4bDrop the index pub_id_idx (Microsoft SQL Server).

DROP INDEX titles.pub_id_idx;

To drop an index in Oracle Database, IBM Db2 Database, or PostgreSQL:

Listing 12.4cDrop the index pub_id_idx (Oracle, Db2, or PostgreSQL).

DROP INDEX pub_id_idx;

Tips for DROP INDEX