[EDIT: Fixed... see 3rd post]
Hello,
The VirtueMart Orders page (backend), is very slow (30+ seconds to display, 10 orders per page, around 6000 total orders). Individual orders open up fast.
How do I troubleshoot this?
Thanks
[J3.8.10/VM3.2.15 PHP 7.1/ DB 5.5.5-10.1.35-MariaDB]
Turned debugging on. I found the query below takes very long to execute (51108.24 ms)
SELECT SQL_CALC_FOUND_ROWS o.*, CONCAT_WS(' ',u.company,u.first_name,u.middle_name,u.last_name) AS order_name ,u.email as order_email,
pm.payment_name AS payment_method,
sm.shipment_name AS shipment_method,
u.company AS company,
u.city AS city,
u.zip AS zip,
u.phone_1 AS phone,
st.address_type AS st_type,
st.company AS st_company,
st.city AS st_city,
st.zip AS st_zip,
u.customer_note AS customer_note
FROM jos_virtuemart_orders as o
LEFT JOIN jos_virtuemart_order_userinfos as u
ON u.virtuemart_order_id = o.virtuemart_order_id
AND u.address_type="BT"
LEFT JOIN jos_virtuemart_order_userinfos as st
ON st.virtuemart_order_id = o.virtuemart_order_id
AND st.address_type="ST"
LEFT JOIN jos_virtuemart_paymentmethods_el_gr as pm
ON o.virtuemart_paymentmethod_id = pm.virtuemart_paymentmethod_id
LEFT JOIN jos_virtuemart_shipmentmethods_el_gr as sm
ON o.virtuemart_shipmentmethod_id = sm.virtuemart_shipmentmethod_id
ORDER BY created_on DESC
LIMIT 0, 10
Any ideas?
FIXED....
One of the tables involved did not have a primary key or any indexes.
Probably my fault because I've been doing manual data migrations.
thank you for your update