I noticed recently that the administrative section on one of our sites has slowed to a crawl. So much so that it actually times out at various intervals.
This only happens in the admin (front end is ok).
I narrowed this problem down to a query in the Rewards Points module and once I commented out that query the admin ran fine again. (I found this query by looking at my server processes in MySQL by the way.)
I localized the rewards points query that was slowing down the entire admin and listed it below. Once I commented this out to test, the admin ran fine again. With it active the admin slows to a crawl.
I realize this query is used to keep things up-to-date automatically from within the admin (as far as points are concerned) but is there a way to get it to operate more efficiently? Right now it makes it virtually impossible to operate.
The query is found in the "extra functions" area (it's the rewards points function file) around line 25 and looks like this:
PHP Code:
// Remove all reward point history records for deleted orders. Update customers pending points removing any deleted order with points still pending
if($db->Execute("UPDATE ".TABLE_REWARD_CUSTOMER_POINTS." rc SET `pending_points`=`pending_points`-IFNULL((SELECT SUM(`reward_points`) FROM ".TABLE_REWARD_STATUS_TRACK." ph WHERE ph.`customers_id`=rc.`customers_id` AND ph.`status`=0 AND ph.`orders_id` NOT IN (SELECT `orders_id` FROM ".TABLE_ORDERS.")),0);"))
$db->Execute("DELETE FROM ".TABLE_REWARD_STATUS_TRACK." WHERE `orders_id` NOT IN (SELECT `orders_id` FROM ".TABLE_ORDERS.");");
}
How can we optimize this query to run more efficiently?
We've got about 8000 customers in the database with tens of thousands of rewards points (obviously).
Bookmarks