SQL: Visual QuickStart Guide

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:

• Searched frequently

• Sorted frequently

• Regularly used in joins

In general, indexes are inappropriate for columns that:

• Accept only a few distinct values (gender or state, for example)

• Rarely are used 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 may need ...