We have a site at https://www.belmetric.com that we have been running successfully for over 10 years. It is currently running Zen 1.5.4, with PHP 5.6.29 and mysql 5.1.73-log.
The site is pretty fast on the customer side, but the admin side - specifically the order list - is painfully slow. Getting a slow query report from MySQL shows some slow queries:
This one I know is the order page load. It takes a long time to load (query is over 11 seconds) the first time the page is requested, and then is OK until another order posts - I assume this is because it is cached by mysql. It also loads 20 rows.
# Time: 200427 9:56:50
# User@Host: xxxxxxxxx[xxxxxxxxxx] @ localhost []
# Query_time: 11.104898 Lock_time: 0.000128 Rows_sent: 20 Rows_examined: 538580
SET timestamp=1587999410;
select o.orders_id, o.customers_id, o.customers_name, o.payment_method, o.shipping_method, o.date_purchased, o.last_modified, o.currency, o.currency_value, s.orders_status_name, ot.text as order_total, o.customers_company, o.customers_email_address, o.customers_street_address, o.delivery_company, o.delivery_name, o.delivery_street_address, o.billing_company, o.billing_name, o.billing_street_address, o.payment_module_code, o.shipping_module_code, o.ip_address from (orders_status s, orders o ) left join orders_total ot on (o.orders_id = ot.orders_id and ot.class = 'ot_total') where (o.orders_status = s.orders_status_id and s.language_id = '1') order by o.orders_id DESC limit 40, 20;
This one is a mystery to me -
# Time: 200427 9:57:58
# User@Host: xxxxxxxxx[xxxxxxxxxx] @ localhost []
# Query_time: 13.385325 Lock_time: 0.000112 Rows_sent: 106778 Rows_examined: 645298
SET timestamp=1587999478;
select o.orders_id, o.customers_id, o.customers_name, o.payment_method, o.shipping_method, o.date_purchased, o.last_modified, o.currency, o.currency_value, s.orders_status_name, ot.text as order_total, o.customers_company, o.customers_email_address, o.customers_street_address, o.delivery_company, o.delivery_name, o.delivery_street_address, o.billing_company, o.billing_name, o.billing_street_address, o.payment_module_code, o.shipping_module_code, o.ip_address
from (orders_status s, orders o )
left join orders_total ot on (o.orders_id = ot.orders_id and ot.class = 'ot_total')
where (o.orders_status = s.orders_status_id and s.language_id = '1') order by o.orders_id DESC;
It looks like it sends 106778 rows, because it is not limited.
Do we know what is looking for so many orders, and is there a way of optimizing this so it runs faster?
TIA
Bookmarks