Introduction to Database Indexes

By Chris Smith

Another reason for creating indexes is for tables that reference other tables. For example, in a CMS you might have a news table that looks something like this:

CREATE TABLE newsitem (
  newsid INT PRIMARY KEY,
  newstitle VARCHAR(255),
  newscontent TEXT,
  authorid INT,
  newsdate TIMESTAMP
);

and another table for authors:

CREATE TABLE authors (
  authorid INT PRIMARY KEY,
  username VARCHAR(255),
  firstname VARCHAR(255),
  lastname VARCHAR(255)
);

A query like this:

SELECT newstitle, firstname, lastname FROM newsitem n, authors a WHERE n.authorid=a.authorid;

... will be take advantage of an index on the newsitem authorid:

CREATE INDEX newsitem_authorid ON newsitem(authorid);

This allows the database to very quickly match the records from the 'newsitem' table to the 'authors' table. In database terminology this is called a table join - you should index any fields involved in a table join like this.

Since the 'authorid' in the authors table is a primary key, it is already indexed. The same goes for the 'newsid' in the news table, so we don't need to look at those cases.

On a side note, table aliases make things a lot easier to see what's happening. Using 'newsitem n' and 'authors a' means we don't have to write:

SELECT newstitle, firstname, lastname FROM newsitem, authors WHERE newsitem.authorid=authors.authorid;

for more complicated queries where more tables are referenced this can be extremely helpful and make things really easy to follow.

In a more complicated example, a news item could exist in multiple categories, so in a design like this:

CREATE TABLE newsitem (
  newsid INT PRIMARY KEY,
  newstitle VARCHAR(255),
  newscontent TEXT,
  authorid INT,
  newsdate TIMESTAMP
);

CREATE TABLE newsitem_categories (
  newsid INT,
  categoryid INT
);

CREATE TABLE categories (
  categoryid INT PRIMARY KEY,
  categoryname VARCHAR(255)
);

This query:

SELECT n.newstitle, c.categoryname FROM categories c, newsitem_categories nc, newsitem n WHERE c.categoryid=nc.categoryid AND nc.newsid=n.newsid;

... will show all category names and newstitles for each category.

To make this particular query fast we need to check we have an index on:

newsitem newsid
newsitem_categories newsid
newsitem_categories categoryid
categories categoryid

Note: Because the newsitem newsid and the categories categoryid fields are primary keys, they already have indexes.

We need to check there are indexes on the "join" table - newsitem_categories

This will do it:

CREATE INDEX newscat_news ON newsitem_categories(newsid);
CREATE INDEX newscat_cats ON newsitem_categories(categoryid);