Thanks for reading! So I'm kind of banging my head against a wall but I firstly want to say that I think this is a DB setup problem rather than the code as even when stripped back to bare bones WC the queries are still slow so no interfering 3rd party code.
Basically I have a WP/WooCommerce website that has quite a lot of data (11GB DB) and the WooCommerce core admin queries like listing orders on the orders page is taking 2 seconds plus other queries which totals to 9 seconds in DB queries. I really want to speed up these queries so query monitor plugin doesn't have any slow queries or at very least get much nearer to the 0.2s target but I have asked around and seem to be getting quite a few varied responses such as Redis, object cache and tools to add indexes but someone else fairly enough said that this will obviously add caching and speed up the queries but should the cache be cleared it will still be slow which doesn't (I guess) really solve the original issue?
I'm not a DB admin expert by any means so I've previously just chucked memory at the VPS (48GB) and set the InnoDB pool size as high as mysqltuner told me to at 28GB but I get the feeling this is wrong as most google results suggest it should be 8GB or at very max be the same GB amount as the DB is big and the DB is back to being slow! Any ideas on what's going wrong? I've seen other things like increasing innodb_io_capacity (which is currently set to 200 but max is set to 2000 and the VPS is using an SSD) but mysqltuner has not mentioned these variables and I've not updated these values before. Would just like to know if I do just need to implement Redis etc or to start I do need more memory or I do need to change some variables or I need to do them all! Happy to provide what info I can.
Thanks in advance, Brad
