Best Practices: Database Indexing - Page 2

by: Tim Perdue

January 8, 2001

SELECT * FROM mytable WHERE category_id=1;

Even the most rudimentary indexing strategy means you will create a simple index on category_id:

CREATE INDEX mytable_categoryid 
	ON mytable (category_id);

Very simple right? Now what if you are going to use multiple criteria to select from the database?

SELECT * FROM mytable 
	WHERE category_id=1 AND user_id=2;

You might be tempted to just create another index on user_id, but that would be sub-optimal. You need to create a multi-column index.

CREATE INDEX mytable_categoryid_userid 
	ON mytable (category_id,user_id);

Are you noticing my naming convention yet? I use tablename_field1name_field2name. You'll understand why I do that in a minute.

Now that you've built indices on the appropriate columns, you should verify that your database is using them as expected. This is easy to do in most databases - just use the EXPLAIN command:

EXPLAIN
	SELECT * FROM mytable 
	WHERE category_id=1 AND user_id=2;

This is what Postgres 7.1 returns (exactly as I expected).

NOTICE:  QUERY PLAN:

Index Scan using mytable_categoryid_userid on 
	mytable  (cost=0.00..2.02 rows=1 width=16)

EXPLAIN

If you boil that down, essentially postgres is using an index (always a good start), and furthermore it is using the second index I created. Because of the way I named my index, I can tell immediately that it is using the right index.

Now what if your query involves an ORDER BY clause? Believe it or not, most databases will take advantage of an index on the ordered by field.

SELECT * FROM mytable 
	WHERE category_id=1 AND user_id=2
	ORDER BY adddate DESC;

Confused yet? Don't be. Add another multi-column index that includes the fields in the WHERE clause as well as the ORDER BY clause and you're in business:

« Previous Page

Comment and Contribute

0 Comments (click to add your comment)

Your comment has been submitted and is pending approval.

Author:

Tim Perdue

Comment:

Name/nickname:
Email:

Comment:

(Maximum characters: 1200). You have characters left.