When to Index
Go Up to Working with Indexes
An index on a column can mean the difference between an immediate response to a query and a long wait, as the length of time it takes to search the whole table is directly proportional to the number of rows in the table. So why not index every column? The main drawbacks are that indexes consume additional disk space, and inserting, deleting, and updating data takes longer on indexed columns than on non-indexed columns. The reason is that the index must be updated each time the data in the indexed column changes, and each time a row is added to or deleted from the table.
Nevertheless, the overhead of indexes is usually outweighed by the boost in performance for data retrieval queries. You should create an index on a column when:
- Search conditions frequently reference the column.
- Join conditions frequently reference the column.
ORDER BYstatements frequently use the column to sort data.
You do not need to create an index for:
- Columns that are seldom referenced in search conditions.
- Frequently updated non-key columns.
- Columns that have a small number of possible values.