Slow query in mysql

[semide]

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

[DrByte]

When looking at this same issue on another very large site I observed 2 factors:

1. Adding these indexes seemed to help in some cases:

ALTER TABLE orders ADD INDEX on_status_date_id (orders_status,date_purchased,orders_id);
ALTER TABLE orders_total ADD INDEX on_ot_id_class (orders_id,class);

2. I think the secondary issue is that the query's joins to other large tables (particularly orders_total) is what's making it slow. Not that the query is slow, but the pagination calculation with split_page_results (which re-runs the queries to determine pagination indicators) slows it down.

[lindasdd]

I find the order page and the customers page to load so slowly that I totally avoid them.

My guess is that the query pulls ALL customers and ALL orders and then paginates rather than just pulling the first page.

If I wasn't worried about overriding core pages I would totally change the customer and order pages and instead make them a landing page with a search field. Then only display the results of the search. That is how the pages get used anyway (at least by me and my team).