Now that we've seen why we should use indexes, let's look at why we shouldn't use them. They can actually slow down your database (some databases may actually choose to ignore the index if there's no reason to use it).

A table like this:

CREATE TABLE news (
newsid INT PRIMARY KEY,
newstitle VARCHAR(255),
newscontent TEXT,
active CHAR(1),
featured CHAR(1),
newsdate TIMESTAMP
);

Looks pretty standard. The 'active' field tells us whether the news item is active and ready to be viewed on the site. So should we should create an index on this field for a query like this:

SELECT newsid, newstitle FROM news WHERE active='1';

No, we shouldn't. If most of your content is live, this index will take up extra space and slow the query down because almost all of the fields match this criteria. Imagine 500 news items in the database with 495 being active, it's quicker to eliminate the ones that aren't active than it is to list all of the active ones (if you do have an index on the 'active' field, some databases will choose to ignore it anyway because it will slow the query down).

The featured field tells us whether the news item should feature on the front page. Should we index this field? Yes. Most of our content is not featured, so an index on the 'featured' column will be quite useful.


Other examples of when to index a field include if you're going to order by it in a query.

To get the most recent news items, we do a query like this:

SELECT newtitle, newscontent FROM news ORDER BY newsdate DESC;

Creating an index on 'newsdate' will allow the database to quickly sort the results so it can fetch the items in the right order.


Indexing can be a bit tricky to get right, there are tools available for each database to help you work out if it's working as it should.


Written for Interspire