I have a wordpress woocommerce website that has around 500k products. Currently, when I am trying to load products under certain category(around 30k products), it doesn't load and gives me server timeout, when I am trying to load a category with just 83 products, it is taking more than 30 seconds to show the products.
I used query monitor to analyse the queries, it says there are 945 duplicate queries just for category with 83 products. For categories with larger products the page is not loading so query monitor is not able to show results.
I asked my developer for suggestion, he says I will have to buy dedicated server since shared hosting is bad. He is recommending me against optimising my database. I am currently using Bluehost's wordpress customised server with 6 cpus and 10 gb RAM while bluehost is recommending me to optimise my database rather than going for dedicated server.
I don't know much about woocommerce or wordpress, but what can be solutions to the problem? I have wp-super cache enabled, it did improve to some extent. Will building indexes solve my problem for page load time or should I go with dedicated server to reach page load time under 3 seconds?
I would really be grateful if someone can suggest me some alternatives? I don't want to go with magento, instead I want to stay with woocommerce