MySQL Errors MAX_JOIN_SIZE

Question

I am asking this question on behalf of a small group of my users who have this problem.

As soon as the script they use receives the 21st identifier, it generates the following error:

SELECT will examine more than MAX_JOIN_SIZE rows; check your WHERE and use SET SQL_BIG_SELECTS = 1 or SET SQL_MAX_JOIN_SIZE = # if SELECT is good

I researched this as much as possible and found something like an answer: http://dev.mysql.com/doc/refman/5.0/en/set-option.html

The problem is that they are on shared hosting, so they cannot change their MySQL settings to fix errors.

Is there anything I can write to my script so that they don't have this problem?

This is a function that generates a database query based on which modules are loaded: $ sql = 'SELECT a.id as id, a.address as address';

$query = 'SELECT' . ' name AS module_name' . ', databasename AS module_database' . ', pregmatch AS module_pregmatch' . ', pregmatch2 AS module_pregmatch2' . ', html AS module_html' . ', sqlselect AS database_sqlselect' . ', sqljoin AS database_sqljoin' . ', sqlupdatewithvalue AS database_sqlupdatewithvalue' . ', sqlupdatenovalue AS database_sqlupdatenovalue' . ' FROM #__aqsgmeta_modules' . ' WHERE enabled = 1' . ' ORDER BY id'; $db->setQuery($query); $results = $db->loadObjectList(); if (count($results) != 0) { foreach ($results as $result) { $sqlselect .= ', '; $sqlselect .= $result->database_sqlselect; $sqljoin .= ' '; $result->database_sqljoin = preg_replace('/\{DATABASENAME\}/Ui', $result->module_database, $result->database_sqljoin); if (!(preg_match("/" . $result->database_sqljoin . "/Ui", $sqljoin))) $sqljoin .= $result->database_sqljoin; } } if ($use_sh404sef) $sqlselect .= ', g.oldurl AS sefurl'; $sql .= $sqlselect; $sql .= ' FROM #__aqsgmeta_address AS a'; $sql .= $sqljoin; if ($use_sh404sef) $sql .= ' LEFT JOIN #__redirection AS g ON g.newurl = a.address'; $sql .= //. ' WHERE a.id IN (' . $cids . ')' ' WHERE a.id = ' . $id . ' ORDER BY a.address asc,a.id ' ; $db->setQuery($sql); $rows = $db->loadObjectList(); 

Answer 1

MAX_JOIN_SIZE is the security lock commonly used for shared hosts.

This will not allow you to accidentally run long queries that will hang on the server.

Run this command:

 SET OPTION SQL_BIG_SELECTS = 1 

before running a query that you know to return a lot of values.

Answer 2

MAX_JOIN_SIZE gets hit when MySQL calculates the Cartesian product of the join, rather than the actual expected records. Therefore, if you join a massive table to another massive table, it will creep up. Use indexes and views to reduce a possible table if it is really that big.

More details here: MySQL - SQL_BIG_SELECTS