When was the last time you had a cloud nine moment?
Passing school with flying colors.. or when your girlfriend / boyfriend accepted your proposal?
I had a cloud nine moment two weeks ago! I was speaking at WordCamp Mumbai 2017 – India’s biggest WordCamp.
The topic was something close to my heart, and something I deal with everyday – lessons learnt optimizing WordPress for customer success.
It’s about problems I faced dealing with large WordPress databases and how I tackled them.
What encouraged me to speak at WordCamp Mumbai?
Developing a WordPress plugin is a great learning experience. You get to extend a superb framework, focus on writing code that solves customers’ problems and work with great team members.
At StoreApps, developers not only write code, but also support customers. Resolving customer queries has given me some of the biggest lessons.
So all that is my daily routine, and it’s quite exciting.
But do you know what’s more exciting for me? Working with large databases and solving complex database problems!
Our plugins are used by more than 35000 users. And I’ve seen a lot of challenges working with large WooCommerce stores and popular WordPress sites.
Thankfully, I was able to solve these challenges and learnt a lot.
But wait, just like me, there would be hundreds of other developers facing similar issues working with WordPress and WooCommerce.
So I decided to share my lessons with others.
And what better platform than a speaking opportunity at India’s largest WordCamp!
The three problems that gave me sleepless nights (With Solutions & Lessons Learnt From Them)
Finally, here are the three problems which I was talking about. So let’s begin with them one-by-one.
Reducing page load time from 3 minutes to milliseconds…
Problem
I faced a situation with Smart Coupons plugin wherein a simple functionality of displaying coupons on the crucial pages of the store (i.e. cart, checkout & my-account pages) stalled the checkout process.
Smart Coupons is a plugin for creating and handling of coupons and gift certificates in bulk for a WooCommerce store.
Solution
Now, using WP Query would have resulted in multiple JOINs as for showing the available coupons for a specific user requires multiple meta conditions to be evaluated.
So, instead of using the ideal way of querying the database i.e. WP Query, I wrote custom SQL queries.
Also, in the custom queries, what I did was:
- Instead of checking all the meta conditions in a single MySQL query, I simply evaluated the first meta condition
- After that, I stored the comma separated list of post_ids (coupon ids) in the options table
- And then, I simply map reduced the same set of post_ids by evaluating each of the other meta conditions
This I did until I got a final set of post_ids that needed to be displayed for the specific user.
Enhancing the solution
This did solve the page load issues. However, to get the page load under milliseconds, as suggested by our friend, I had to redefine the problem.
Instead of showing all the coupons that the user is eligible for,
I set a limit to the number of coupons that would be displayed to users on the cart & checkout pages.
How a timeout error became the best selling feature of our plugin?
Problem
For processing very huge database update, I had written custom queries as using core WordPress functions would possibly become huge overhead.
Example: If one had to slash down the prices of all the products in his/her store by 40%, then they simply need to select ‘Price’, ‘decrease by 40%’ and hit the update button. This is easily possible using our plugin Smart Manager, designed to make bulk updates on your WooCommerce stores easier.
But, the batch update process started getting stalled and giving timeout errors whenever anyone was trying to update 1k products or the entire store at a time.
Initially I started looking at optimizing the queries but that didn’t help.
My situation was similar to a Takeshi’s Castle contestant. No matter how much I tried, I kept falling in water.
Solution
It is said that sometimes you have to move out of the problem and take a birds eye view of the problem to find the exact cause.
I did the same and figured out that the actual problem was
not at the query level but at the request level.
So, what I did was instead of a single request doing all the updates, I broke the same into multiple successive AJAX calls doing smaller batch of updates and which completely eliminated the timeout errors.
Enhancing the solution
Now, this approach of breaking down single request into multiple smaller AJAX requests not only solved the timeout error but also improved the batch update UX.
Now the store manager was being updated about the progress of the update which simply increased their confidence in the product.
Also, the same approach has enabled Smart Manager to handle batch updates for WooCommerce store of any shape and size and made Smart Manager to become one of the top selling plugins of StoreApps.
Problem
For any reporting plugin its very crucial to give not only accurate but quick reporting of stats. Now, getting reporting stats required multiple queries involving joining of 2 major WordPress tables i.e. posts and postmeta.
As already seen above, JOINs are so expensive.
In Smart Reporter (our reporting solution for WooCommerce), we were showing 20 different reporting statistics in a single page view and that too on page load.
Solution
So, I followed the same approach as most reporting solutions do follow which was creating summary table i.e. a flat structure custom table.
This table would contain summary of all the data required by the plugin and thus eliminate the use of joins and improve page load times.
Also, to keep these summary tables updated we made use of WordPress actions and filters.
Lessons summary
- Follow best practices as much as possible
- No queries in loop
- Avoid large complex joins
- Do more at the MySQL level than at the PHP level
- Go deeper in code
- Summary / Custom / Temp tables
- Attention to UX – responsiveness, notifications…
- Are you solving the right problem?
What’s your pick?
I’m sure you have dealt with some huge problems and found solutions to it. Let us know about it in our comments section below. It would be really valuable to the readers.