Monday, 20 August 2007
One of the simplest things you can do speed up database access is create appropriate indexes.
There are several aspects to this. Firstly, you need to identify which columns are used for queries on a given table; in
particular, which columns appear in the
WHERE clause of time consuming queries. If a query is only done once, or the
table only has five rows in it so queries are always quick then there is no benefit to adding indexes. It's not just
SELECT statements that need checking —
WHERE clauses too.
Having identified which columns are used in queries, it is important to also note which combinations are used. A database engine
will tend to only use one index per table (though some can use more, depending on the query), so if your time-consuming queries use
multiple columns from the same table in the
WHERE clause, then it's probably better to have an index that covers all of
them rather than separate indexes on each.
The cost of indexes
Adding an index to a table does have a downside. Any modification to a table with an index (such as inserts and deletes) will be
slower, as the database will have to update the index, and the index will occupy space in the database. That can be a high price to
pay for faster queries if the table is modified frequently. In particular, an index that is never used, or covers the same columns
as another index is just dead weight. It is important to remember that
PRIMARY KEY and
automatically have indexes that cover them.
Timing is everything
As with every optimization, it is important to profile both before and after any change, and this includes checking the performance of the rest of the application too. Don't be afraid to remove an index if it isn't helping, and bear in mind that it's also possible to improve performance by rewriting queries, particularly where the are joins or subqueries involved.