Reducing Database Query Time

Question

Looking for some suggestions here. Have a client who has been on WooCommerce for about a year and a half now. We built the new site for them.

They started adding a new product line of furniture. Each item has between 700 - 800 product variation combinations.

Since adding about 8 new products from the line, when you go to view the products list in the admin, it takes forever to load.

If you quick edit 1 product and say add it to a second category and hit update, it is taking on average 10.2 - 10.8 seconds (according to Query Monitor) for the query to complete. Realizing that there are anywhere from 700-800 variations which need to iterate through, the potential for it take longer if understandable.

I have reverted back to Twenty Seventeen theme, disabled all plugins except for woocommerce and tested again. Query update time remained unchanged.

The site is on a dedicated server running CENT OS Intel Xeon E3-1270 v5 Quad-Core with 16GB RAM

Within wp-config.php I have set the following:

define('WP_MEMORY_LIMIT', '1024M');
define( 'WP_MAX_MEMORY_LIMIT', '2000M' );

Within PHP.ini configuration I have the following:

max_execution_time 500
max_input_time 60
max_input_vars 10000
memory_limit 2000M
post_max_size 512M

We are running MariaDB as a dropin replacement for MySQL

I am not sure if the way to speed things up is changing a setting in PHP.ini overall increase in RAM on the server, offloading the DB to its own server or what, but 10.2 seconds to update a product is unacceptable and I need to find a way to speed this up.

I appreciate any suggestions...

Answer 1

While there are specific MySQL tuning options available, and of course the possibility of moving your database to a dedicated server; ultimately the issue is the way WooCommerce handles variations. WooCommerce itself warns about the impact of performance when creating variations, you even have to confirm your choice when creating variable products.

So first off I'd look at the variety of product customizers available for WooCommerce. Most of them do not actually create variations in the true WooCommerce sense, so there typically is far less MySQL overhead for the product.

Secondly, I'd review the product variations with an eye to data normalization. Can you present the products in a better structure that will reduce the number of variations required for a given product?

Typically I see that store admins tend to over complicate the product. If they are selling a T-shirt in both men's and women's cuts, multiple sizes and multiple colors they tend to treat it as a single product which creates far too many variations.

For example a single T-shirt available in (Men's or Women's), size (Small, Medium, Large, X-Large), color (Red, Blue, Green) creates a single product with 24 permutations/variations. Displaying, updating, or creating the product involves 25 different records; the parent record and each of the various records.

Creating two different products (i.e. Men's and Women's) reduces the total overhead to only 13 records for each product. Greatly limiting the overhead for each product.

Obviously, this is a very simplified example but it does demonstrate the principle and the impact it has on performance.