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.

Brandon Savage is the author of Mastering Object Oriented PHP and Practical Design Patterns in PHP

There are currently no comments.

Copyright © 2023 by Brandon Savage. All rights reserved.