PHPBuilder - Best Practices: Database Indexing

RSS Twitter

Best Practices: Database Indexing

by: Tim Perdue
January 8, 2001

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


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.

Next Page »

Comment and Contribute

Your comment has been submitted and is pending approval.

Tim Perdue



(Maximum characters: 1200). You have characters left.