Any of the tech geeks that have worked for me in the past have heard me say it a million times.

“Check your indexes , people!”

It is the single-most overlooked issue that often yields the biggest performance gains on any SQL driven data system like WordPress, for example.

I cannot tell you how many times a junior coder or systems person has walked into my office and asked me to help them resolve an application performance problem.   The first thing I ask them is if the problem is data related.  The very next question is “have you checked your indexes?”.  More times than I can count  they find an improper or missing index.  Using SQL tools like ‘explain’ and building a proper index for the query that is causing problems can yield big performance gains with little effort.

Today I ran into a performance problem using my WordPress Dev Kit plugin that serves plugin updates to my WordPress plugin customers.   The dashboard on the sales site was horrendously slow.   The admin panel would take up to a full minute to load.    Building the right index on my data table brought that time down to less than 3 seconds.

Finding The Problem

I started by installing and enabling Query Monitor on my site.   This allowed me to see what was taking so long to execute.    The first report, a red herring, was the 18,000 entries from the wp_options table that was being loaded.

Turns out there were 15,000+ entries for _site_transient_brute_loginable , all of which were set to autoload.  That means WordPress was loading all 15,000 outdated and obsolete Brute Protect transients.

After deleting those 15,000 entries, Query Monitor brought me to the real culprit.  There were 4 database queries that were running slowly.  ONE of the queries was coming from my WPDK plugin.    It was only selecting 20 records , the 20 most recent entries, from a table with only a few data points.    However that table has 800,000+ rows and grows by a few thousand on a daily basis.

Fixing The Problem

The problem is that even though I was only asking for 20 records the “select the newest” was the problem.   MySQL had to read the ENTIRE database to find which 20 records were the newest.   Adding a simple index to the table fixed that issue.   Building an index on the lastupdated field allows the order by lastupdated DESC clause to utilize the index and read only 20 nodes from the index to fetch the record.    It is MUCH faster.  As in 57 seconds faster on a 60 second query.

As I’ve said before… CHECK YOUR INDEXES PEOPLE!