The SELECT would examine more than MAX_JOIN_SIZE rows – MySQL Error

MAX_JOIN_SIZE rows

One of my caching scripts started failing the other day and when I ran it in phpmyadmin I got the below error.

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

What the heck? This was something I had never seen before.

After some investigation work I found this snippet on the mysql documentation site.

Basically if you are running a large query that hits a big number of rows the query engine will spit up that error.

How to fix the MAX_JOIN_SIZE rows error

There are two ways I found that this can be fixed.

1./ Run this query before the big query that is throwing the error.

SET SQL_BIG_SELECTS=1;

2./ Tune your query to use a where clause or filter down the rows returned somehow!

My query was doing a LIMIT 10 ( like a top 10 ) query, I managed to add in a WHERE clause with a date filter and the problem went away.

Rob StGeorge
Senior SQL Server Database Administrator residing in Auckland, NZ

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.