Two of the other issues Brad and Jo were experiencing were tied somewhat together, the slow back end and front end.
Because both websites are large e-commerce platforms, it is to be expected that after some time, the databases would start to slow down and decrease the website performance if not managed and cleaned periodically.
The reason for this is WooCommerce generates a lot of entries in the database table for each client and order. For each order placed, there are about 10 records created and stored in the database.
A medium-size e-commerce website with 60,000 orders has anywhere between 500k and 600k entries in the post meta table. Our websites had over 4 million entries.
Some of the steps we took to de-clutter the database:
- Optimise the database table or repair
- Reduced the number of post revisions
- Deleted trash, unused tags, categories, orders over 1-year-old, transients, spam comments
Identified unused plugins/themes and deleted, also replaced plugins that provide basic functionality with code to reduce bloat
The results of this process were somewhat decent, however far from ideal. We managed to reduce load time on the front end with these optimizations but reduced the post meta table very little.
After more investigation, we identified the cause of the backend slowness and why the post meta table was so large.
The YITH Multivendor plugin used for the multivendor capabilities of the website was duplicating the same information several times, thus increasing the size of the post meta table.
If a client buys a product from 3 different vendors, there are 4 orders are created.
One order is created for the WooCommerce admin and then another sub-order for each vendor item.
This means if normally WooCommerce creates 10 entries for a single order, in our case it would create 40 entries in the database.
Our solution was two-fold.
Create indexes in the database for faster access to the information so that when WooCommerce queries the database search for the order information, it pulls it faster.
Rewrite the WoCommerce query to provide faster results.
Results, reduced backend overall load time to a few seconds, order search still being around 10 seconds due to the high number of entries in the database table.
After several months of troubleshooting and working with a fairly limited budget, we have decided to approach the problem from a different angle.
We decided to split the database from the server that was hosting the files and PHP processes of the website.
We have decided to go with a highly optimized database only server running SQL 8 for hosting the database whilst hosting the files and PHP services on a custom stack Nginx server.