Recently in more (usually cheap) hosts more of my clients reported a strange error, wich showed up recently, withouth notice.
The error message is like this:
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
followed by the failing query.
Long story short: the error message has nothing to do with Joomla itself, it is just a setting on the server wich causing these errors. In order to save their resources the hosting companies began to use two obscure MySQL settings, introduced relatively recently.
The usual settings causing this are located im MySQL config file and are looking generally like:
SQL_BIG_SELECTS=0 - which forbids use of big selects
MAX_JOIN_SIZE=300000 - or something like that limiting, sometimes severely the JOIN sizes.
There are multiple solutions, like calling the customer support, submitting a ticket, using the .htaccess - but generally these are useless, the settings are there to enforce the hosting company's usage policies.
The second option is to switch hosts ;).
As a last resort you can hack in the failing component's code. Generally you are on the lucky side, and not the Joomla core, but one of non core components are triggering the errors. You can do a hardcore hack by adding the below code directly to the code, or you can use one of MVC override plugins available in JED to do so. Anyway, is not something for rookies. If you decided to go this way, put your belts on, do backups and locate the model associated with the failing view.
Locate the function getListQuery(), and just below the very first database query, after the line:
$db = $this->getDbo();
Insert these two lines:
$db->setQuery("SET SQL_BIG_SELECTS=1");
$db->execute();
This code basically sets for the rest of user session the SQL_BIG_SELECTS flag to ON, and MySQL will NOT throw in your face that nasty error message again. To be noted, that MySQL user sessions aren't the same as the Joomla user sessions, so you might need to hack your component's multiple models - both on frontend and backend.
In my experience this little code hack works on most cases, but if not, you still can switch your hoster, and to choose a Joomla friendly one, like this one.
If you are decided to fix it globally, and open to hack in the Joomla core itself - I DO NOT RECOMMEND THAT - you can use this big hammer:
Make all necesary precautions - save, backup, drop some salt over your left shoulder, etc, anddit the file libraries\joomla\database\driver\mysqli.php and insert the line
mysqli_query($this->connection,"SET SQL_BIG_SELECTS = 1;" );
in the function connect after the line 168
mysqli_query($this->connection, "SET @@SESSION.sql_mode = '';");
Don't tell your Mama what you just did, and don't complain - the trick works, but it comes with absolutely no warranties. And, of course, will be gone after first update.
Better find a decent host...
