10/23/2006
MySQL Database Performance - ZenCart Sales Report
One of my client's ZenCart installs was loading the server down so much to generate sales reports with the Sales Report v1 contribution that the public side was noticeably less responsive for the couple of minutes it took to build the report. The site has been running for over three years and contains 9k orders, so there is a lot of data to weed through even for a weekly report. A quick look at the slow query log listed the following query as the culprit:
FROM orders_products_attributes opa, orders o, orders_products op
WHERE o.orders_id = opa.orders_id
AND op.orders_products_id = opa.orders_products_id
AND o.date_purchased >= '2006-10-18T00:00:00'
AND o.date_purchased < '2006-10-19T00:00:00'
AND op.products_id =179
GROUP BY products_options_values
ORDER BY orders_products_id;
Running this query through EXPLAIN shows 20978 rows examined. In this case, thats 20978 rows per day included in the sales report.
The main problem with this query is the ORDER BY clause which cannot utilize an exisiting index. Adding the following index:
limits the rows examined for each query to 68 (results).
This simple change allows the sales report to load in seconds as opposed to minutes with no noticeable lag on the public side.
The create table SQL for the current version of ZenCart (v1.3.5) contains the following index in orders_products_attributes:
KEY idx_orders_id_prod_id_zen (orders_id,orders_products_id)
...
This index is used in the query in question. In my tests it was only slightly slower than the single column index above. EXPLAIN showed about 270 rows examined with the two column index on my dataset while the single column index only examined 68 rows.
Popularity: 19%
