News:

Looking for documentation? Take a look on our wiki

Main Menu

SQL_CALC_FOUND_ROWS and many records slow query

Started by Pirosoft, January 31, 2013, 13:15:24 PM

Previous topic - Next topic

Pirosoft

Hi, i have a  problem, in a category i have many products (80000). When the system must make pagination use a query like this:

SELECT SQL_CALC_FOUND_ROWS  * FROM `jos_virtuemart_products_it_it` as l JOIN `jos_virtuemart_products` AS p using (`virtuemart_product_id`) LEFT JOIN `jos_virtuemart_product_categories` ON p.`virtuemart_product_id` = `jos_virtuemart_product_categories`.`virtuemart_product_id`
    LEFT JOIN `jos_virtuemart_categories_it_it` as c ON c.`virtuemart_category_id` = `jos_virtuemart_product_categories`.`virtuemart_category_id` LEFT JOIN `jos_virtuemart_product_shoppergroups` ON p.`virtuemart_product_id` = `jos_virtuemart_product_shoppergroups`.`virtuemart_product_id`
    LEFT  OUTER JOIN `jos_virtuemart_shoppergroups` as s ON s.`virtuemart_shoppergroup_id` = `jos_virtuemart_product_shoppergroups`.`virtuemart_shoppergroup_id` WHERE ( p.`published`="1"  AND  `jos_virtuemart_product_categories`.`virtuemart_category_id` = 3 AND (s.`virtuemart_shoppergroup_id`= "3" OR (s.`virtuemart_shoppergroup_id`) IS NULL )) group by p.`virtuemart_product_id` ORDER BY p.virtuemart_product_id ASC LIMIT 20, 10

But this query it's slow, and in internet i found this link:
http://www.mysqlperformanceblog.com/2007/08/28/to-sql_calc_found_rows-or-not-to-sql_calc_found_rows/

And my question is if it's possibile change the query in vmmodel.php function exeSortSearchListQuery() SQL_CALC_FOUND_ROWS with count(*)
in the code:
if($this->_withCount){
   $q = 'SELECT SQL_CALC_FOUND_ROWS '.$select.$joinedTables;
}
?