The next time we should create an index is in 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 be 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

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

So 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);