WordPress Database Optimization Case Study – Slow Queries from 1 minute to 20 ms!

In this first WordPress database optimization case study, average response time improves by 50% after the slow MySQL queries go from 1 minute down to 20ms.

2020 has been crazy. To deal with the insanity, many people are trying to spend their time wisely.

No, I don't mean Instagram scrolling or dicking around on Facebook.

I'm talking about learning. Ideally learning useful constructive things that will enrich your life and those you care about, even humanity as a whole!

WordPress gets used as an LMS which stands for Learning Management System. It's really easy to get started with Learndash or LifterLMS.

We have had a huge influx of learning sites which do online or virtual courses that have performance issues.

This case study focuses on an online course site that teaches you how to take care of your car, all virtually.

Meet Mike, the creator of this cool learning site and hear his story about going from his site crashing to stable and faster!

We'll call it Mike's Automotive Academy.

WordPress Database Optimization Case Study – The Site Owner

Mike runs an automotive learning system to train young drivers how to maintain their vehicles.

Mike's system is super popular because it has a gaming component made by GamiPress (pro version).

Students can earn achievements like trophies for certain accomplishments. They can earn vehicles and road trips.

It's really clever. So is Mike.

There is this cool leaderboard showing all of the driving students and who's winning and who's losing.

Naturally, this leaderboard gets checked all of the time because students want to see if they beat their friend or classmate.

When 3000 students log in around 9 AM, they all check the leaderboard.

Guess what happens?

The site would slow to a halt, barely crawling forward to deliver the next lesson for students.

Sometimes it would crash. Actually, quite regularly it would crash 🙁

What would happen next?

Mike would get floods of emails from students and teachers.

Inundated with phone calls, Mike would get super stressed out from these site crashes.

He literally has a gigantic screen in his office showing the number of active users in Google Analytics.

Needless to say, Mike takes his business very seriously.

His reputation was on the line, so was his revenue.

Mike said it was OK if the site was slow, but it mustn't crash.

Crashing websites is how you lose your customers and recurring subscribers.

Deals were on the line that had taken months to negotiate.

Mike worked really hard to build this awesome learning portal to train young drivers.

Teaching young people useful knowledge is a beautiful thing which should be rewarded.

Mike's learning portal should be bringing him joy and prosperity, not hopelessness and despair!

He was already using very high-end hosting costing $600 a month.

He had already migrated from other supposed ‘dedicated hosting' so he did not want to move again.

Mike wanted to tackle the root cause of these crashes so his students and instructors could start enjoying the material again.

The PHP+MySQL response time in Mike's hosting dashboard was showing 1.5 seconds.

Online Course Website Troubleshooting Approach

We take a holistic approach to troubleshooting WordPress and WooCommerce so we can cover all of the major culprits.

  • New Relic audit – we are certified (verify here)
  • Server audit – we have sysadmin skills so we check areas most don't!
  • Plugin audit
  • Database audit

Online Website Troubleshooting Diagnosis

After our emergency diagnosis using our tried and tested tools, we found the following issues

  • Multiple database problems with the engine and resource allocation
  • Poorly coded-plugins executing slow PHP functions
  • MySQL database queries taking forever – like over a minute!

These slow database queries were all related to the gamification Mike used. The gamification that has been critical to his success. We could not just remove this feature, it was his entire business.

We had to fix the root cause so Mike could keep his customers.

Here are some New Relic screenshots showing how bad the performance was once we got started.

A 1.7 second average response time from the server hosting the online learning system Mike built :O

You see all of that yellow? That is all MySQL WordPress database query calls taking up more than half of the total response time!

Slow table of the leaderboard requests taking about 2 minutes to load, those poor students waiting that long!


The specifically slow queries took almost a minute for a few of them, they were really, really heavy.

No wonder the site was slowing to a halt and crashing!

You see that big dip in the middle of the graph? Mike's site crashed at that point 🙁

Online Website Troubleshooting Results

New results, we put the database optimizations live at 9:45 – the effects were immediate!

2 hours later the average response time reduced by 50% going from 1.7 seconds to 870 ms.

Look at all of that yellow – disappeared!

Not gonna lie, we felt like Houdini a little bit 😉

Here is what it looked like 24 hours earlier, soo much yellow from those slow database queries in WordPress and the LMS system.

The database queries which were taking almost a minute before are now taking around 20 ms after the optimizations.

That's an incredible improvement reducing query time from 58,000 ms to 20 ms.

The PHP+MySQL average response time in the hosting dashboard is now 600 ms 1 week later!

Look at how dramatic the processing time fell over 7 days.

Now from the past 4 days the average response time is closer to 600ms!

Not bad for the first WordPress databsae optimiziation case study, eh?

The power of database optimization should not be underestimated.

Using a thorough data-driven approach, we were able to give Mike back his energy.

No more stressing about the site crashing first thing in the morning – can you imagine a worse way to start your day?

The calls from customers and students complaining all stopped.

Mike cracked a smile when the results came in.

Now business is back to normal for Mike and he can focus on creating content and spending time with his family.

Conclusion

The WordPress database matters in so many ways. We mention it several times in why your site speed still sucks post, website performance can be hard to crack.

Often your website will need a solid session of TLC (Tender Loving Care) before it starts to perform well if it's been crashing and slowing down.

The database is just one piece of the puzzle, but one you want to get right and make sure isn't the missing piece.

Our troubleshooting technique doesn't let any bottleneck hide. We will find the most critical performance issues on your site and resolve them as elegantly as possible.

We can repeat this process over and over until you are happy with the speed of your site.

Get help troubleshooting your WordPress site to see how we can get your online course site back on track!

Work with Pros!

#OptimizewithHeart♥  

Leave a Comment