In diagrams, you can create, edit, or delete table indexes, which gain fast access to specific information in a table. 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.
You 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. |
To modify table indexes, use Index Editor.
See also:
Diagram Objects: Index Editor | Index Manager