MySQL Optimization

Hello,
We have an online shop that uses the salesrule table to store salesrules (discounts). A salesrule can be applied automatically or with a coupon code (salesrule_coupon). It can be assigned to one or more websites (core_website, relation table is salesrule_website).
This online shop uses the same database and tables for more than 20 websites we have (core_website).

The problem is that the query (attached) takes 3-10 seconds on the live server.

(4 million entries in salesrule_coupon and 1000 in salesrule table on the live server)

So this database should be optimized. My suggestion would be to check if there is any potential of adding indices.
Maybe we could also split the tables so we have one salesrule and salesrule_coupon table per website. The structure of the tables should not change for the administration, so we need still all salesrules in one table. Maybe we could create a trigger that would reflect any change of salesrule and salesrule_coupon in the websites table salesrule_website_1 and salesrule_coupon_website_1 etc.
If trigger is not possible maybe we could consider adding partitioning.
About the recuiter
Member since May 20, 2018
Fendy Setiawan
from Alagoas, Brazil

---

---

Looking for help? Checkout our video tutorial
How to search and apply for jobs

How to apply? Do you have more questions about the Job?
See frequently asked questions