I am getting "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 SQL_MAX_JOIN_SIZE=# if the SELECT is okay" on the following query.
mysql> SELECT ord.*, pt.authorize_transaction_id FROM store_orders as ord LEFT OUTER JOIN store_payments as pt ON ord.id = pt.order_id WHERE (ord.user_id like '2' and ord.order_reference like '%' and ord.status_id like '%' and (pt.authorize_transaction_id like '%' || pt.authorize_transaction_id is NULL)) ORDER BY created_at DESC; sql_big_sqlect is 'OFF' but max_join_size is set to 524288000. Both the tables have 748 rows each. So the query should scan less than a million rows (748 * 748), in which case sql_big_selects should have no effect on the query since max_join_size is set way higher than one million. Is it a BUG or am I missing something here? I am using version 5.1.30-ndb-6.3.20-cluster-gpl-log (64 bit machine). Presently I have resolved it by setting max_join_size to its default value 18446744073709551615, which turns 'ON' sql_big_selects for each session. (http://dev.mysql.com/doc/refman/5.1/en/server-session-variables.html#sysvar_sql_big_selects) -------------------------- I have attached more details below: OUTPUT from explain command: +----+-------------+-------+------+---------------+------+---------+------+------+----------------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+------+---------------+------+---------+------+------+----------------------------------------------+ | 1 | SIMPLE | ord | ALL | NULL | NULL | NULL | NULL | 748 | Using where; Using temporary; Using filesort | | 1 | SIMPLE | pt | ALL | NULL | NULL | NULL | NULL | 748 | Using where | +----+-------------+-------+------+---------------+------+---------+------+------+----------------------------------------------+ 2 rows in set (0.02 sec) mysql> show variables like "sql_big_select%"; +-----------------+-------+ | Variable_name | Value | +-----------------+-------+ | sql_big_selects | OFF | +-----------------+-------+ 1 row in set (0.00 sec) mysql> select count(*) from store_orders; +----------+ | count(*) | +----------+ | 748 | +----------+ 1 row in set (0.00 sec) mysql> select count(*) from store_payments; +----------+ | count(*) | +----------+ | 748 | +----------+ 1 row in set (0.01 sec) mysql> show variables like 'max_join_size'; +---------------+-----------+ | Variable_name | Value | +---------------+-----------+ | max_join_size | 524288000 | +---------------+-----------+ 1 row in set (0.00 sec) mysql> show variables like 'sql_max_join_size'; +-------------------+-----------+ | Variable_name | Value | +-------------------+-----------+ | sql_max_join_size | 524288000 | +-------------------+-----------+ 1 row in set (0.00 sec) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=arch...@jab.org
