Error: 1104 - SQLSTATE: 42000 ER_TOO_BIG_SELECT
March 11th, 2009
Description:
Message: 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
The actual problem: “Maximum Join Size” is not the same as “Number of Records Returned.” If you have to join two 1000-row tables, you’ll have a maximum join size of around 1000×1000 = 1000000, since 1000000 different row combinations have to be compared, even if only one row matches! MySQL will optimize away many of these rows if you’ve got a WHERE clause, but still, the join size in queries often ends up being enormous.
The reason MySQLCC sets the max join size is to avoid user error — the logic is, if you’ve got a join size of 1000000 you’re probably doing something wrong. Sometimes optimization solves this, sometimes the query is completely the opposite of what you intended, but often, you actually *do* want a join size of a million. The selection in server settings only goes up to 2^24 = 16 million (as far as I can tell, this number was selected arbitrarily), and even that is not always enough (the MySQL variable MAX_JOIN_SIZE is optional and has a limit of 2^32 = 4 billion, so any value from 1 to 4 billion should be available).
When using the command-line client, MAX_JOIN_SIZE is ignored by default, since SQL_BIG_SELECTS=1. But MySQLCC enforces a MAX_JOIN_SIZE, even when you don’t want one. The solution: Apply the patch I posted earlier. Then set the “Maximum join size” to 0; it will set SQL_BIG_SELECTS=1 and therefore not return an error on big queries.
That said, in most cases you shouldn’t go above a join size of a million if only 1 record is returned (except for GROUP BY queries, I suppose). If you’re getting this error, chances are your query could be optimized considerably.