I recently had the pleasure of indexing our company’s sites’ tables. The custom CMS I inherited has some really brilliant code, but it also has quite a large amount of idiotic code blocks as well. In this case, the database was not thought out as best it could have.

I was able to reduce some ridiculously long queries from 10 seconds down to 2 seconds with a few well placed indices. While 2 seconds isn’t anything to brag about. It’s quite an improvement. A database index is a lookup table that is created to help query information more quickly.

Before jumping in. One thing to note about indices is that there is an expense. If you go ahead and index your entire table it will likely take longer to run that query than a non-indexed table. So you have to be discerning about what you index.

The rule of thumb is, anything that alters the query, typically a WHERE clause or ORDER or GROUP, is a potential candidate for an index. For me, the rest of the process was trial and error, I went through all the potential candidates in my longest queries and tried various combinations until I came up with the quickest time for each query. There may be an algorithm for calculating the best columns to index. But this worked.

phpMyAdmin doesn’t make it easy to find your indices.

( Don’t get any funny ideas, this is just a typical WordPress install. )

1) Open a table, click “Structure” at the top.

2) At the bottom of the page is a tiny link that says ”Indexes”. You can manage existing indices from here.

3) To add an index check to see if there’s a “More” menu on the column you’d like to index…

Enjoy!