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 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
