MySQL: "The SELECT would examine more than MAX_JOIN_SIZE rows"

Question

I am using PHP and MySQL. In my program there is a select query involving joins. When I run it on localhost it's working fine but when I upload it on my server and try to execute it then 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

How can I correct this?

Answer 1

Try running as a query previous executing your select:

SET SQL_BIG_SELECTS=1

Is this really executing over a huge dataset? If not this should be solved in a different way.

Answer 2

The parameter's effect is documented at http://dev.mysql.com/doc/refman/5.0/en/server-system-variables.html#sysvar_max_join_size.

You should filter the involved records more strictly (so there are less records involved in each part of the query). If possible start with the table where you can filter out the most records with a simple WHERE-clause.