Orders page very slow [solved]

[EvanGR]

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

[EvanGR]

Turned debugging on. I found the query below takes very long to execute (51108.24 ms)

Code Select Expand


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?

[EvanGR]

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.

[AH]

thank you for your update