MySQL error max_join_size causing trouble? Bobcares has an answer to your problem. 

At Bobcares, we offer solutions for every query, big and small, as a part of our Server Management Service.

Let’s take a look at how our Support Team recently helped out a customer with MySQL error max_join_size.

How to resolve MySQL error max_join_size

Have you been coming across the MySQL error message stating “The SELECT would examine more than MAX_JOIN_SIZE rows”? If the answer is yes, you are in the right place. Our Support Engineers have been helping customers who have been seeing the following error message:

ERROR 1104 (42000): The SELECT would examine more than MAX_JOIN_SIZE rows;
check your WHERE and use SET SQL_BIG_SELECTS=1 or SET MAX_JOIN_SIZE=#
if the SELECT is okay

Interestingly, this error occurs when queries examine a large number of rows. Moreover, the error also occurs while joining two tables on fields without indexes.

For instance, if the SQL query looks like this:

WHERE a.field_a = b.field_b

In the above example, we do not have indexes on both field_a and field_b. In other words, MySQL has to construct a large temporary table with a large number of rows. Fortunately, we can fix this by adding indexes to both join fields.

Our Support Techs have come up with another way to steer clear of this error. It involves running a SET SQL_BIG_SELECTS=1 statement first. This will skip the check thereby ignoring the value for MAX_JOIN_SIZE, thereby allowing us to skip the error message.

Alternately, we can set MAX_JOIN_SIZE to a bigger value that will resolve the issue as well. Our Support Engineers would also like to point out that setting MAX_JOIN_SIZE to any value other than DEFAULT will result in SQL_BIG_SELECTS being set to zero.

[Looking for a solution to another query? We are just a click away.]

Conclusion

To sum up, the skilled Support Engineers at Bobcares demonstrated how to resolve MySQL error max_join_size.