MySQL MAX_JOIN_SIZE errors

Question

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();

Answer 1

Could you post your Query Here ? I have had that issue once too and it was because of a nested selected which, internally in MySql was being handled as a join. Most of the time, you can work around this by changing your query a bit.

Answer 2

The MAX_JOIN_SIZE gets hit when MySQL calculates the Cartesian product of a join, not the actual expected records back. Therefore, if you're joining a massive table to another massive table, this will creep up. Use indexes and views to pare down the possible table hits if it's really that large.

See more here: http://stackoverflow.com/questions/950465/mysql-sqlbigselects

Answer 3

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 OPTION SQL_BIG_SELECTS = 1

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