Posted by admin on June 29, 2009 at 06:38
There’s suboptimal query in admin/orders.php page which retrieves information about last orders. Unfortunately query is constructed in such way that MySQL scans whole orders, orders_products and orders_total table. As always it’s not a problem until orders table gets big ( for example 50 000 entries ). If You happen to have at least 10 000 orders ( I wish You that 
), You probably get impatient each time You request admin/orders.php page.
Good news is that there is quick fix. Just add index on (orders_id, class) on orders_total table to speed up the query. Here’s MySQL statement which does that for You:
ALTER TABLE orders_total ADD INDEX idx_oid_class(orders_id, class)
The query is still suboptimal, but at least its quite fast until orders table gets really big (500 000 orders? It’ll depend on Your server).
Continue Reading
Posted by admin on June 24, 2009 at 16:53
This is mainly note for me for future reference (ideas seem to pop out of my head quite quickly…). In future, I plan to make following modules:
- to track user with cookie (even if he/she is not logged in). [Done, see: User Tracking Interface]
- to perform REPAIR TABLE on all tables from Admin panel
- to split customers into groups randomly (for randomized tests). For example, We want to know if new ‘Buy Now’ button attracts customers to buy stuff. We could create two groups [old_button,new_button] and present them two different buttons. Contribution would provide basic statistics like order conversion ratio, average order size, average order total etc.
- to split images across few subdomains. It’ll speed up page generation by browser.
Continue Reading
Posted by admin on June 23, 2009 at 14:34
Store Credit is great module, unfortunately it’s preety inefecient in some places. For example, on every page request of admin/store_credit.php whole customers table is read, and for each customer additional SELECT and UPDATE query is executed to calculate and update pending points, even if the customer does not have any pending points!
It’s not a big deal when You have up to 1000 customers, but when there are more then 100 000 customers page loads in 30 seconds (store_credit.php executes over 2 x customer count, or in this example over 200 000 queries on each request).
To address that, change following code in admin/store_credit.php:
function store_pending_rewards
() {
global $db;
$customers = $db->Execute("SELECT customers_id FROM " . TABLE_CUSTOMERS . " ORDER BY customers_id ASC");
while (!$customers->EOF) {
$customers_id = $customers->fields[‘customers_id’];
$pending_rewards = $this->get_pending_rewards($customers_id);
$db->Execute("UPDATE " . TABLE_STORE_CREDIT . "
SET pending = " . $pending_rewards . "
WHERE customers_id = " . $customers_id . "
LIMIT 1");
$customers->MoveNext();
}
}
to:
function store_pending_rewards
() {
global $db;
$customers = $db->Execute("SELECT DISTINCT customers_id FROM " . TABLE_SC_REWARD_POINT_LOGS . " ORDER BY customers_id ASC");
while (!$customers->EOF) {
$customers_id = $customers->fields[‘customers_id’];
$pending_rewards = $this->get_pending_rewards($customers_id);
if($pending_rewards == 0.0) { $customers->MoveNext(); continue; }
$db->Execute("UPDATE " . TABLE_STORE_CREDIT . "
SET pending = " . $pending_rewards . "
WHERE customers_id = " . $customers_id . "
LIMIT 1");
$customers->MoveNext();
}
}
Thanks to that, only customers who actually have some pending points will be processed.
Continue Reading
Posted by admin on June 23, 2009 at 11:14
Version v1.5.1 of Query Log is available. You can download it from: www.data-diggers.com/contribs/query-log/downloads/querylog-current.zip .
What’s new in this version:
- You can now close ‘calculator’ layer (it’s the tool that highlights queries that match given regular expression)
- You can now disable query logging completely. In previous versions Query Log kept all queries in memory, no matter if You wanted to log them (display them) or not.
Continue Reading
Posted by admin on June 22, 2009 at 15:46
I noticed that following query takes long time to execute:
SELECT DISTINCT op.products_id
FROM orders o, orders_products op, products p
WHERE o.customers_id = ’2345′
AND o.orders_id = op.orders_id
AND op.products_id = p.products_id
AND p.products_status = ’1′
GROUP BY products_id
ORDER BY o.date_purchased DESC
LIMIT 6
I noticed that there’s no index on orders table on customers_id field. Without it MySQL has to scan whole table to find orders by customers_id (in this case ’2345′). If orders table is small there’s no problem, but when it has 10 000 or more entries it can noticably slow down Zen Cart. So, let’s add index on that table
ALTER TABLE `orders` ADD INDEX `idx_cid_datepurchased`(`customers_id`, `date_purchased`);
I included date_purchased in index, because some pages in admin area will use it for searching all orders of given customer.
In my case it decreased query time from 0.3 sec to 0.08 sec. It’s still too much, but it’s better then nothing.
Continue Reading
