You can use an index to gain fast access to specific information in a database table. An index is a structure that orders the values of one or more columns in a database table, for example the last name (lname) column of the employee table. If you were looking for a specific employee by his or her last name, the index would help you get that information faster than if you had to search all the rows in the table.
The index provides pointers to the data values stored in specified columns of the table, and then orders those pointers according to the sort order you specify. The database uses the index much as you use an index in a book: it searches the index to find a particular value and then follows the pointer to the row containing that value.
In database diagrams, you can create, edit, or delete each type of index in the Indexes/Keys property page for a selected table. An index is saved in the database when you save the table that it is attached to, or when you save the database diagram in which that table appears.
As a general rule, you should create an index on a table only if the data in the indexed columns will be queried frequently. Indexes take up disk space and slow the adding, deleting, and updating of rows. In most situations, the speed advantages of indexes for data retrieval greatly outweigh these disadvantages. However, if your application updates data very frequently or if you have disk space constraints, you might want to limit the number of indexes.
Before creating an index, you must determine what columns to use and what type of index to create.
Index columnsYou can create indexes based on a single column or on multiple columns in a database table. Multiple-column indexes enable you to distinguish between rows in which one column may have the same value.
Indexes are also helpful if you often search or sort by two or more columns at a time. For example, if you often set criteria for last name and first name columns in the same query, it makes sense to create a multiple-column index on those two columns.
To determine the usefulness of an index:
- Examine the WHERE and JOIN clauses of your queries. Each column included in either clause is a possible candidate for an index.
- Experiment with the new index to examine its effect on the performance of running queries.
- Consider the number of indexes already created on your table. It is best to avoid a large number of indexes on a single table.
- Examine the definitions of the indexes already created on your table. It is best to avoid overlapping indexes that contain shared columns.
- Examine the number of unique data values in a column and compare that number with the number of rows in the table. The result is the selectivity of that column, which can help you decide if a column is a candidate for an index and, if so, what type of index.
Depending on the functionality of your database, you can create three types of indexes - unique, primary key, and clustered - in Database Designer.
Although a unique index will help locate information, for the best performance results it is recommended that you use primary key or unique constraints instead.
A unique index is one in which no two rows are permitted to have the same index value.
Most databases prevent you from saving a table with a newly created unique index when there are duplicate key values in the existing data. Your database may also prevent the addition of new data that would create duplicate key values in the table. For example, if you create a unique index on the employee’s last name (lname) in the employee table, then no two employees can share the same last name.
A database table often has a column or combination of columns whose value uniquely identifies each row in the table. This column is called the primary key of the table.
Defining a primary key for a table in a database diagram automatically creates a primary key index that is a specific type of unique index. This index requires each value in the primary key to be unique. It also permits fast access to data when you use the primary key index in queries.
In a clustered index, the physical order of the rows in the table is the same as the logical (indexed) order of the key values. A table can contain only one clustered index.
If an index is not clustered, the physical order of the rows in the table does not match the logical order of the key values. A clustered index usually provides faster access to data than does a nonclustered index.