Author Topic: Orders page very slow [solved]  (Read 435 times)

EvanGR

  • Jr. Member
  • **
  • Posts: 282
Orders page very slow [solved]
« on: September 12, 2018, 14:49:35 pm »
[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

  • Jr. Member
  • **
  • Posts: 282
Re: Orders page very slow
« Reply #1 on: September 12, 2018, 22:24:43 pm »
Turned debugging on. I found the query below takes very long to execute (51108.24 ms)

Code: [Select]
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

  • Jr. Member
  • **
  • Posts: 282
Re: Orders page very slow
« Reply #2 on: September 13, 2018, 08:24:06 am »
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

  • Global Moderator
  • Sr. Member
  • *
  • Posts: 3025
  • VirtueMart Version: 3.6.3
Re: Orders page very slow [fixed]
« Reply #3 on: September 13, 2018, 09:29:01 am »
thank you for your update
regards
A

Joomla 3.9.12
php 7.2