Best Practices: Database Indexing

Like most PHP developers out there, I started off using databases to
store relatively simple data structures for my dynamic sites. PHP’s
slickness and ease of database connectivity is no doubt a major reason
for its wild success, and also probably a reason why databases get
created and used without a huge amount of design and documentation.

In my next couple of articles, I’m going to attempt to talk about
the best way to use databases with
PHP. I’m not going to cover normalization, as Barry Wise wrote a
fantastic article on that topic.

Instead, I’m going to cover 3 other, arguably less important, advanced
topics:

1. Proper use of indices (indexes) – this article
2. Transactions – next week
3. Foreign Keys – two weeks

Indexing

Let’s start with indexing. I was asked the other day by a fairly advanced
programmer what indices are, and that scared me. I knew I had to write
up an article as this programmer is clearly not alone. Tens of thousands of
developers (most probably using MySQL) have little formal training with
databases and yet make a living developing on them for clients.

The very first thing you should do, if you do nothing more, is build an
index on any column in a table that is included in a WHERE clause.

CREATE TABLE mytable (
id serial primary key,
category_id int not null default 0,
user_id int not null default 0,
adddate int not null default 0
);

That’s a pretty simple table that will suffice for most of this discussion.