»  Home  »  Programming  »  Introduction to Database Indexes
Introduction to Database Indexes
By Chris Smith | Published 02/15/2006 | Programming | Rating: ratingfullratingfullratingfullratingfullratingfull Unrated |
Multi-Column Indexes

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.
Pages: « Back  1 2 3 4 5  Next » 
Next: When Not To Index »

Spread The Word

Article Series

This article is part 3 of a 4 part series. Other articles in this series are shown below:
  1. An Introduction to Databases
  2. An Introduction to Database Variable Types
  3. Introduction to Database Indexes
  4. Database Joins
Web designers: Learn how to attract more clients and profit like the big guys. Subscribe to our newsletter.