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 !
$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 !
Sorry, i had to spit the posted answer, i have a bug sometime with codes copy in the forum
How about posting the full order.php with your changes
At least people can then test
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.
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.
QuoteMy 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.
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
I have asked VM dev to take a look