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/ (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;
}
?