Big shop orders list Slow Query problem and solution

[Studio 42]

HI,
I had a request form a user for slow admin orders list display.
The problem is the "SELECT SQL_CALC_FOUND_ROWS" in the main query
Doing this mean count all rows with this query. But this query load a plenty of fields.
I decided to split this in 2 queries.
To do this, here my changes :


$this->setGetCount(false);
before
$this->_data = $this->exeSortSearchListQuery(0,$select,$from,$whereString,'',$ordering);
And after
$countQuery = 'SELECT COUNT("o.virtuemart_order_id") FROM #__virtuemart_orders as o ';
if ($this->search){
$countQuery .='
LEFT JOIN #__virtuemart_order_userinfos as u
ON u.virtuemart_order_id = o.virtuemart_order_id AND u.address_type="BT"
LEFT JOIN #__virtuemart_order_userinfos as st
ON st.virtuemart_order_id = o.virtuemart_order_id AND st.address_type="ST" ';
$countQuery .= $whereString;
}
// echo $countQuery ; jexit();
$db = JFactory::getDbo();
$db->setQuery($countQuery);
$this->_total = (int)$db->loadResult();

Before changes, the query needed 6189.59 ms for a shop with 650 000+ order user info records
After changes the 2 queries need 2.73 ms for the result + 58.94 ms for the count, in total 61ms
If you do a search in orders view the result is always at minimum 30X faster !

[Studio 42]

$this->setGetCount(false);
before
$this->_data = $this->exeSortSearchListQuery(0,$select,$from,$whereString,'',$ordering);
And after
$countQuery = 'SELECT COUNT("o.virtuemart_order_id") FROM #__virtuemart_orders as o ';
if ($this->search){
$countQuery .='
LEFT JOIN #__virtuemart_order_userinfos as u
ON u.virtuemart_order_id = o.virtuemart_order_id AND u.address_type="BT"
LEFT JOIN #__virtuemart_order_userinfos as st
ON st.virtuemart_order_id = o.virtuemart_order_id AND st.address_type="ST" ';
$countQuery .= $whereString;
}
// echo $countQuery ; jexit();
$db = JFactory::getDbo();
$db->setQuery($countQuery);
$this->_total = (int)$db->loadResult();

Before changes, the query needed 6189.59 ms for a shop with 650 000+ order user info records
After changes the 2 queries need 2.73 ms for the result + 58.94 ms for the count, in total 61ms
If you do a search in orders view the result is always at minimum 30X faster !

[Studio 42]

Sorry, i had to spit the posted answer, i have a bug sometime with codes copy in the forum

[AH]

How about posting the full order.php with your changes

At least people can then test

[Studio 42]

Hi,
My file is not the last beta.
But it's simple to understand for a dev, i think.

In administrator/components/com_virtuemart/models/orders.php function getOrdersList add a line before and copy the other part after.

[derek webster]

I updated VM today and remerged these changes - heres the latest file
this is /administrator/components/com_virtuemart/models/orders.php from VirtueMart 3.8.6 10373
with the speed optimisations in place.

[AH]

My file is not the last beta.

Yes I updated in my test version -

Everything continues to work as expected with the 'SELECT SQL_CALC_FOUND_ROWS' never being invoked when using order list.

[sirius]

Hi
I confirm way faster on 3.8.7 too (same source file as 3.8.6)
with more than 16300 orders and pagination set to 400

[AH]

I have asked VM dev to take a look