So far I’ve got around 8,000 products in my WooCommerce and the product page on the front end takes several seconds to load, even after caching.
My VPS host says that I should have multiple databases.
Is that the right method and if so, how is it possible?
Any suggestions on speeding up my database queries or simply speeding up the front end would be great.
This is really a server question and not particularly WordPress.
You’re running into a MySQL server speed bottleneck at 1and1. Sorry, but they’re well known as a slow shared host, and for that reason I doubt their VPSs are any better.
Caching and a CDN will only do so much for you. You need more horsepowwer in the form of a better and faster MySQL server, and if you’re already paying for a VPS, move to a better VPS host, like Media Temple.
Once there, with root access, you can use a utility such as http://mysqltuner.com to log and analyze the performance of the MySQL server and adjust server memory and database query cache parameters in the configuration file my.cnf. You will also be able to adjust apache – in httpd.conf – to handle high loads.
There is little sense in trying to optimize the database queries made by Woo Commerce or WordPress; any changes made will need to be made again after updates.
I have two solutions which I would suggest which should resolve your problem. Turn to the use of elasticsearch.com to populate all product indexes. At the same time you dramatically improve the search capabilities of your website (if integrated correctly). I would make sure to place the elasticsearch solution on a different server.
My thoughts are that your caching system is not setup correctly or your server is overloaded. A solution which might be the cheapest (option #2) would be to use the free caching servers from cloudflare.com. This typically will fix your problem in the most cost effective way if you dont want to deal with server optimizations.
Caching with Varnish is the best solution, however you must avoid caching the header cart snippet and the cart/checkout pages. I think that WooCommerce should be configured with Varnish Edge Side Includes. To exclude checkout Follow these instructions:
http://docs.woothemes.com/document/configuring-caching-plugins/#section-3