Introduction to Database Indexes

By Chris Smith

We could create an index like this:

CREATE INDEX news_cats ON newsitem_categories(newsid, categoryid);

However, doing this limits some ways the index can be used. A query against the table that uses both 'newsid' and 'categoryid' will be able to use this index. A query against the table that only gets the 'newsid' will be able to use the index.

A query against that table that only gets the 'categoryid' will not be able to use the index.

For a table like this:

CREATE TABLE example (
  a int,
  b int,
  c int
);

With this index:

CREATE INDEX example_index ON example(a,b,c);

• It will be used when you check against 'a'.
• It will be used when you check against 'a' and 'b'.
• It will be used when you check against 'a', 'b' and 'c'.
• It will not be used if you check against 'b' and 'c', or if you only check 'b' or you only check 'c'.
• It will be used when you check against 'a' and 'c' but only for the 'a' column - it won't be used to check the 'c' column as well.
  

A query against 'a' OR 'b' like this:

SELECT a,b,c FROM example where a=1 OR b=2;

• Will only be able to use the index to check the 'a' column as well - it won't be able to use it to check the 'b' column.

Multi-column indexes have quite specific uses, so check their use carefully.