SQL Run

Database Programming

« Previous

Home

Contents

» Next

12. Indexes

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:

• Searched (WHERE)
• Sorted (ORDER BY)
• Grouped (GROUP BY)
• Used in joins (JOIN)
• Used to calculate order statistics (MIN(), MAX(), or the median, for example)

In general, indexes are inappropriate for columns that:

• Accept only a few distinct values (gender, state, or marital_status, for example)
• Are used rarely in queries
• Are part of a small table with few rows

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

• SQL’s indexing statements modify database objects, so your database administrator might need to grant you permission to run them.
• An index never changes data; it’s merely a fast access path to the data.
• A table can have zero or more indexes.
• Ideally, you create all a table’s indexes when you create the table. In practice, index management is an iterative process. Typically, only vital indexes are created along with the table. Other indexes are added or deleted over time as performance problems grow or ebb and users’ access patterns change. DBMSs provide testing and benchmarking tools to determine the effectiveness of indexes.
• Don’t create any more indexes than you need. The DBMS must update (and possibly reorganize) an index after you INSERT, UPDATE, or DELETE rows in a table (see Chapter 10). As the number of indexes on a table grows, row-modification performance degrades as the DBMS spends more and more time maintaining indexes. In general, you shouldn’t create more than about a dozen indexes for a table.
• Your DBMS will maintain and use indexes automatically after they’re created. No additional actions are required by users or SQL programmers to reflect data changes in all relevant indexes.
• Indexes are transparent to the user and SQL programmer. The absence or presence of an index doesn’t require a change in the wording of any SQL statement.
• An index can reference one or more columns in a table. An index that references a single column is a simple index; an index that references multiple columns is a composite index. Columns in a composite index need not be adjacent in the table. A single index can’t span multiple tables.
• The order in which columns appear in a composite index is significant. A composite index applies only to the group of columns on which it’s defined, not to each column individually or the same columns in different order.

• You can create multiple composite indexes that use the same columns if you specify distinctly different combinations of the columns. The following two statements, for example, specify valid combinations for the same table:

  CREATE INDEX au_name_idx1 ON authors (au_fname, au_lname);

  CREATE INDEX au_name_idx2 ON authors (au_lname, au_fname);

• In addition to allowing rapid sorts and searches, an index can ensure uniqueness. A unique index forces the value of the column (or columns) upon which the index is based to be distinct in the table. If you try to create a unique index for column(s) in which duplicate values already exist, then your DBMS will generate an error and refuse to create the index. DBMSs create unique indexes automatically when you define a primary-key constraint or unique constraint.
• A DBMS may or may not create indexes for foreign keys automatically. If not, then you should create these indexes yourself because most joins involve a foreign key.
• All DBMSs implement indexes even though indexes aren’t part of the relational model (and don’t violate any of the model’s rules).

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:

• Type:

  CREATE [UNIQUE] INDEX index
  ON table (index_columns);

  index is the name of the index to create and is a valid SQL identifier. Index names must be unique within a table. For Oracle, Db2, and PostgreSQL, index names must be unique within a database.

  table is the name of the table to create the index for, and index_columns is a list of one or more comma-separated names of the columns to index.

  Specify UNIQUE to create a unique index. UNIQUE causes the DBMS to check for duplicates in index_columns. If table already contains rows with duplicates in index_columns, then the DBMS won’t create the index. If you attempt to INSERT or UPDATE duplicate values in unique index_columns, the DBMS generates an error and cancels the operation.

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:

• Changes to PRIMARY KEY constraints are checked with FOREIGN KEY constraints in related tables.
• Foreign-key columns often are used in join criteria when the data from related tables are combined in queries by matching the FOREIGN KEY column(s) of one table with the PRIMARY KEY or UNIQUE column(s) in the other table.

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

Tips for CREATE INDEX

• Don’t use the terms index and key interchangeably. An index is a physical (hardware-related) mechanism that the DBMS uses to improve performance. A key is a logical (based on data) concept that the DBMS uses to enforce referential integrity and update through views.
• You also can use a unique constraint to prevent duplicate column values; see “Forcing Unique Values with UNIQUE” in Chapter 11.
• Indexes are files stored on-drive and so occupy storage space (possibly a lot of space). But when used properly, indexes are the primary means of reducing drive wear by obviating the need to read large tables sequentially. While a DBMS is creating an index, it uses as much as 1.5 times the space that the associated table occupies (make sure you have room). Most of that space is released after the index is complete.
• Searching a table sequentially (for lack of an index) is called a table scan.
• A clustered index, or clustering index, is an index in which the logical order of the key values determines the physical order of the corresponding rows in a table. In a nonclustered index, the index’s logical order differs from the physical, stored order of the on-drive rows. A table can have at most one clustered index. Clustered indexes usually improve performance, but in some cases they make searches much faster but INSERTs, UPDATEs, and DELETEs much slower.
• Most indexes are implemented as balanced trees, or B-trees. A B-tree is an advanced data structure that minimizes drive read/write operations. Some DBMSs let you specify the data structure to use when constructing an index.

• Microsoft SQL Server and Db2 consider multiple nulls to be duplicates when UNIQUE is specified and allow no more than one null in columns with a unique index. Microsoft Access, Oracle, MySQL, and PostgreSQL allow multiple nulls in such columns.

  Some DBMSs let you create indexes on views (Chapter 13) as well as tables.

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 index is no longer needed because the associated table is much smaller (or was dropped), or users don’t access the index’s columns much anymore.
• The extra time it takes the DBMS to maintain the index after INSERT, UPDATE, or DELETE operations outweighs the speed improvement in retrieval operations that the index provides.

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:

• Type:

  DROP INDEX index
  ON table;

  index is the name of the index to drop, and table is the name of the index’s associated table (Listing 12.4a).

To drop an index in Microsoft SQL Server:

• Type:

  DROP INDEX table.index;

  index is the name of the index to drop, and table is the name of the index’s associated table (Listing 12.4b).

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

• Type:

  DROP INDEX index;

  index is the name of the index to drop (Listing 12.4c).

Tips for DROP INDEX

• You can’t drop indexes that the DBMS creates automatically for PRIMARY KEY and UNIQUE constraints (Chapter 11).