Wordpress is deployed on the LAMP / LEMP stack, which means using the relational MySQL database. However, WooCommerce has a limitation, as if the number of products exceeds 5000 and with the complex filtering structure, the site has noticeable difficulties with the speed of work. At best, this causes discomfort for users, and at worst blocks the opportunity to use the site and ordering products in general.
Until recently, nothing could be done with this: as long as we did not try to change the platform to a more universal one, designed for greater loads. Seems that finding a solution is a simple task, but the cost of this solution exceeds the cost of WordPress by far.
However, we found a way out – to use the NoSQL ElasticSearch database for the most resource-intensive places in the front-end part of the site. Plugin in use for this would be the ElasticPress.
Speaking technically, ElasticSearch is a flat database (NoSQL), with a different data structure. At the same time Elastic under certain conditions can accelerate the execution of requests to it for sample data 10-30 times compared to MySQL. ElasticSearch also contains the ability to index search results, which further speeds up the processing time of the sample.
To handle complex queries, the ElasticPress functionality will need to be expanded, but it handles the typical queries just perfectly. For example:
– search on site (goods)
– sample after filtration (goods)
– output of goods of a certain category
– list of products, orders, pages in the admin panel
– and other requests that are included in WP_Query
As you can see, in the case of ElasticSeaech, the query took 0.3 seconds, while for MySQL it is 1.9 seconds, winning 6 times and this is not even the best result.
– SSH root access to the server running OS Ubuntu / Centos with the hosted site. Unfortunately, this cannot be done on hosting, thus a separate server to host ElasticSearch in required, for example, here, https://www.digitalocean.com/pricing/, the rate depends on the expected load, it can be $10 to $ 40 per month.
– WordPress version is not lower than 4.9.4. Upgrade to the required version should be evaluated depending on a situation.
– WooCommerce version is not lower than 3.0. Upgrade from earlier versions usually takes up to 30 hours of development.
– Presence of Crontab-scheduler of tasks (usually it is given by 99% of hostings).