Search DB query causes MySQL error 1104

[d0ublezer0]

VM2.0.8e
On some hosting providers standard VM search module creates a SQL-query, that raises error 1104, with any of search word:

#1104 - The SELECT would examine more than MAX_JOIN_SIZE rows; check your WHERE and use SET SQL_BIG_SELECTS=1 or SET SQL_MAX_JOIN_SIZE=# if the SELECT is okay

It is possible to optimize this query?
On localhost when debugging, i'we counted 79 columns on query's result. Why so more?
Example of query:

Code Select Expand


SELECT SQL_CALC_FOUND_ROWS * FROM `cm10h_virtuemart_products_ru_ru` as l JOIN `cm10h_virtuemart_products` AS p using (`virtuemart_product_id`) LEFT JOIN `cm10h_virtuemart_product_categories` ON p.`virtuemart_product_id` = `cm10h_virtuemart_product_categories`.`virtuemart_product_id` LEFT JOIN `cm10h_virtuemart_categories_ru_ru` as c ON c.`virtuemart_category_id` = `cm10h_virtuemart_product_categories`.`virtuemart_category_id` LEFT JOIN `cm10h_virtuemart_product_manufacturers` ON p.`virtuemart_product_id` = `cm10h_virtuemart_product_manufacturers`.`virtuemart_product_id` LEFT JOIN `cm10h_virtuemart_manufacturers_ru_ru` as m ON m.`virtuemart_manufacturer_id` = `cm10h_virtuemart_product_manufacturers`.`virtuemart_manufacturer_id` LEFT JOIN `cm10h_virtuemart_product_shoppergroups` ON p.`virtuemart_product_id` = `cm10h_virtuemart_product_shoppergroups`.`virtuemart_product_id` LEFT OUTER JOIN `cm10h_virtuemart_shoppergroups` as s ON s.`virtuemart_shoppergroup_id` = `cm10h_virtuemart_product_shoppergroups`.`virtuemart_shoppergroup_id` WHERE ((`product_name` LIKE "%Компрессор%" AND `product_name` LIKE "%12V%" OR `product_sku` LIKE "%Компрессор%" AND `product_sku` LIKE "%12V%" OR `product_s_desc` LIKE "%Компрессор%" AND `product_s_desc` LIKE "%12V%" OR `category_name` LIKE "%Компрессор%" AND `category_name` LIKE "%12V%" OR `category_description` LIKE "%Компрессор%" AND `category_description` LIKE "%12V%" OR `mf_name` LIKE "%Компрессор%" AND `mf_name` LIKE "%12V%") AND p.`published`="1" AND ( s.`virtuemart_shoppergroup_id`= "1" OR s.`virtuemart_shoppergroup_id` IS NULL ) ) group by p.`virtuemart_product_id` ORDER BY p.virtuemart_product_id ASC

[d0ublezer0]

There is a quick hack if someone has the same problem.
Inside administrator\components\com_virtuemart\helpers\vmmodel.php on line 348 (for 2.0.10) insert this code:

Code Select Expand


//d0ublezer0 workaround about error 1104 max JOIN size
$this->_db->setQuery("SET SESSION SQL_BIG_SELECTS=1");
$this->_db->query();