Database Tip: Create Appropriate Indexes

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 straight-forward SELECT statements that need checking — UPDATE and DELETE statements can have 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 UNIQUE columns 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.

Posted by Anthony Williams
[/ database /] permanent link
Stumble It! | Submit to Reddit | Submit to DZone

Comment on this post

If you liked this post, why not subscribe to the RSS feed or Follow me on Twitter? You can also subscribe to this blog by email using the form on the left.

Design and Content Copyright © 2005-2024 Just Software Solutions Ltd. All rights reserved. | Privacy Policy