Complete Guide to Fixing the max_join_size MySQL Error

Complete Guide to Fixing the max_join_size MySQL Error

When working with the MySQL server, its common to encounter one error or another. One of the errors many admins have to regularly deal with is the “max_join_size” error. The complete error message reads ‘The SELECT would examine more than MAX_JOIN_SIZE rows’.

mysql.png


This error usually happens when the queries that are being executed check a huge amount of rows at once. The error can also surface when two tables on the fields are joined without indexes.

For example, take the following SQL query:

Code:
WHERE a.field_a = b.field_b
Here, there are no indexes on either field_a or field_b. So MySQL has to create a high number of temporary tables that contain high number of rows. And that’s when the error happens as you reach the max joining limit.

You can easily fix this error. You have to run the following statement first:

Code:
SET SQL_BIG_SELECTS=1
It will prompt the SQL to skip the check. Thus, it will ignore the value set in MAX_JOIN_SIZE.

There’s another way of fixing this error. And that is to increase the value of MAX_JOIN_SIZE. Increase it to a value as per your calculation. You won’t get the error message until you reach the new value.

So those were the two ways of fixing the “max_join_size” error. If problem persists, check with your hosting provider.
Author
kumkumsharma
Views
2,429
First release
Last update
Rating
0.00 star(s) 0 ratings

More resources from kumkumsharma

Top