MySql Error 1104 - SET SQL_BIG_SELECTS=1

[crash3903]

I have just encountered an error when testing a new installation on a MySql5 and PHP5 server config - I know this is an old error as far back as version 3 of MySql, but in case anyone is experiencing the same problem there here is a fix

 

When searching for 2 words from the search box (In my case on the left) I experienced this error

 

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

 

I don't have root access to the MySql server so I have added the following in advanced_search_result.php

 

Around line 208 find this

 

  $select_str = "select distinct " . $select_column_list . " m.manufacturers_id, p.products_id, pd.products_name, p.products_price, p.products_tax_class_id, IF(s.status, s.specials_new_products_price, NULL) as specials_new_products_price, IF(s.status, s.specials_new_products_price, p.products_price) as final_price ";

 

Directly above add this

 

// SQL_BIG_SELECTS = 1 - To resolve 1104 MYSQL error messages
tep_db_query("set sql_big_selects=1");

 

So that whole section now looks like this

 

// SQL_BIG_SELECTS = 1 - To resolve 1104 MYSQL error messages
tep_db_query("set sql_big_selects=1");
 $select_str = "select distinct " . $select_column_list . " m.manufacturers_id, p.products_id, pd.products_name, p.products_price, p.products_tax_class_id, IF(s.status, s.specials_new_products_price, NULL) as specials_new_products_price, IF(s.status, s.specials_new_products_price, p.products_price) as final_price ";

 

I hope that helps someone

 

Regards