mysql query max size,MySQL MAX_JOIN_SIZE错误

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

Once the script they are using gets to the 21st ID, it generates the following error:

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 have researched this as much as possible and found something of 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 the errors.

Is there anything I can write into my script so that they do not have this problem?

This is the function that generates the 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();

解决方案

MAX_JOIN_SIZE is a safety catch commonly used on the shared hostings.

It won't let you accidentally run long queries which would hang the server.

Issue this command:

SET SQL_BIG_SELECTS = 1

before running the query you know to return lots of values.