For The Love Of God, Use Keys!

I recently worked on an application I built some time ago. It was built before I had regard for performance or cared about how a MySQL database should be built. As such, it had no indexes.

That changed, especially when I started playing with old data that I wanted to migrate. The read time on 15 rows in a 325-row table was some 0.86 seconds (which isn’t bad but isn’t great). When indexes were applied, however, the read time went down to 0.01 seconds, an 88% decline in read time.

Indexes are easy to add. From your MySQL command prompt all you have to use is the ALTER command, like so:

ALTER TABLE tableName ADD KEY (columnNameHere);

That’s it! Of course, you may want to have some more complex keys (UNIQUE, FULLTEXT, etc) and I recommend you read the MySQL documentation.

Indexes aren’t for everything, but they can help with some things – especially on join clauses and WHERE clauses. So use them. You’ll be thankful you did.

The original work of Brandon Savage.

Related posts:

1. Designing Databases: Picking The Right Data Types

There are currently no comments.