Best Practices: Database Indexing Page 2

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: