Error: 1104 - SQLSTATE: 42000 ER_TOO_BIG_SELECT
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
MySql-Server 08s01 bad handshake, 1043 #08s01bad handshake, cache:104sbi6kja0j:iderror.com/errors/mysql/mysql-server/error-1100-sqlstate-hy000-er_table_not_locked/ er_table_not_locked, cache:4yzzaxw3leuj:iderror.com/errors/mysql/mysql-server/error-1064-sqlstate-42000-er_parse_error/ error: 1064 sqlstate: 42000 (er_parse_error), cache:csfwa4fvkbwj:iderror.com/errors/mysql/mysql-server/error-1043-sqlstate-08s01-er_handshake_error/ 08s01 bad handshake, cache:je-zh-n3h9aj:iderror.com/errors/mysql/mysql-server/error-1069-sqlstate-42000-er_too_many_keys/ mysql "too many keys" archive "max 1", cache:r--zny_n56gj:iderror.com/errors/mysql/mysql-server/error-1075-sqlstate-42000-er_wrong_auto_key/ sql error (1075): incorrect table definition; there can be only one auto column and it must be def, error 1104 (42000), error 1104 (42000) at line 1: the select would examine more than max_join_size rows;, error 1104 (42000) at line 1: the select would examine more than max_join_size rows; check your where and use set sql_big_selects=1, error 1104 (42000): the select would examine more than max_join_size rows, error 1104 42000 mysql command, error 42000 cache, error: 1064 sqlstate: 42000 (er_parse_error), error: 1064 sqlstate: 42000 (er_parse_error) mysql, error: 1104 sqlstate: 42000 (er_too_big_select), error: 1104 sqlstate: 42000 (er_too_big_select) 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 , errore 1104 : er_too_big_select in mysql, er_too_big_select, fix error: 1100 sqlstate: hy000 (er_table_not_locked) when importing database, fix for er_too_big_select, hibernate merge sql error 1064 sqlstate 42000, how to fix error 1100 mysql import, id = error: sqlstate[42000]:, j, lrm-00118: syntax error at '=' at the end of input, lysozyme, mysql error 1100, mysql error 1104, mysql error 1104 (42000), mysql error 1104 how to fix, solution sqlstate 42000, sql error: 1104, sqlstate 42000 (error 100024), sqlstate mysql -sql -java -php
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.