WooCommerce can really give mysql or mariadb a pounding.
On smaller hosting platforms WooCommerce will grind to a halt.
If you have a VPS, here are a few things you can tune, to give you fast database performance. The faster the database goes, the faster WooCommerce goes:
Assuming of course that you’re using innodb (which is a must – see below)
The size should be as large as your wp_postmeta and wp_options tables, or 50% or more of your WooCommerce database. Don’t go above 25% of the RAM in your VPS.
There are a few articles out there saying disable query cache , but I think it really helps the WordPress wp_options table, which WooCommerce stores most of its data in.
If you have a lot more memory in your VPS, try this:
Innodb is the fastest and most reliable database engine for mysql , mariadb, WordPress and WooCommerce. Here’s how to check.
You are looking for engine and you want InnoDB.
If it’s myisam you need to fix it. Use this command in PHPMyAdmin (as above). make sure you have a good backup first.
I wrote another article on adding an index, and clearing transients, find it here.