30

Hey, I've been investigating SQL_BIG_SELECTS, but the MySQL documentation so far has been pretty unhelpful. I'm looking for some insight as to preventing errors like the one below from appearing.

ERROR 1104: The SELECT would examine too many records and probably take a very long time. Check your WHERE and use SET OPTION SQL_BIG_SELECTS=1 if the SELECT is ok

  1. At how many rows does MySQL decide that a query is a "BIG SELECT"?
  2. Will proper indexing usually solve this issue?
  3. Is SQL_BIG_SELECTS considered a "last resort", or is it good practice?
  4. How would someone set "SQL_BIG_SELECTS=1" in configuration (without having to execute the query)?
  5. Are there any other alternatives worth knowing?

Thanks in advance!

Matt
  • 3,778
  • 9
  • 35
  • 36

6 Answers6

43

  1. MySQL determines whether or not a query is a 'big select' based on the value of 'max_join_size'. If the query is likely to have to examine more than this number of rows, it will consider it a 'big select'. Use 'show variables' to view the value of the max join size.

  2. I believe that indexing and particular a good where clause will prevent this problem from occuring.

  3. SQL_BIG_SELECTS is used to prevent users from accidentally executing excessively large queries. It is okay to set it to ON in mysql.cnf or using the command-line option at startup.

  4. You can set SQL_BIG_SELECTS in my.cnf or at server startup. It can also be set on a session basis with SET SESSION SQL_BIG_SELECTS=1.

  5. Not that I can think of. I would just check your query to make sure that you really need to use it. Our servers have it turned on by default, and max_join_size is very large.

Joel Coehoorn
  • 399,467
  • 113
  • 570
  • 794
jonstjohn
  • 59,650
  • 8
  • 43
  • 55
  • 2
    Setting correct indexes on tables used in 'join' can also solve the problem - just did so. – Bojan Bjelic Jan 15 '12 at 14:16
  • Thanks, this not only helped me solving my problem, but also optimizing my complete database structure! – Tum Jun 03 '14 at 10:19
2

You cannot set SQL_BIG_SELECTS in my.cnf or at server startup as it is a session only parameter. I am using MySQL 5.0.60.

Bhavik Shah
  • 2,300
  • 1
  • 17
  • 32
Bing Liu
  • 21
  • 1
1

As someone has post before, you can not set SQL_BIG_SELECTS on my.cnf at server startup. This kind of variable does not support that.

I had a same problem with a Symfony application showing this annoying error:

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

But you can increase the number of varialbe

max_join_size which is related to sql_big_selects

I was able to fix it executing a command as privileged mysql user:

# SET GLOBAL max_join_size=18446744073709551615;

Or you can include it in my.cnf because max_join_size is allowed to set up in configuration file

Well, I hope this can help someone else.

Rubendob
  • 1,614
  • 3
  • 22
  • 34
1

I had more than 2000k records in db, and my query was big-select with exhaustive comparison for duplication removal and updation of certain field...I was told the same by mysql (current version on answer date), and I ended up using index on 2 fields involved in where clause...this should help others too...steps which worked for me were:

  1. Set Index on field
  2. ANALYZE TABLE
  3. run the query

HTH

Zaffar Saffee
  • 6,167
  • 5
  • 39
  • 77
0

Following command works for me

SET GLOBAL max_join_size=18446744073709551615;

But what do i need to put in my.cnf file instead of the command? Btw, i'm using "5.6.12 MySQL Community Server"

Musa
  • 3,944
  • 4
  • 21
  • 27
-1
SET SESSION SQL_BIG_SELECTS =1;# MySQL returned an empty result set (i.e. zero rows).
SELECT a.`HACtrl` , b.`HBCtrl` , c.`HDCtrl`
FROM `HO110BLote` AS a
LEFT JOIN `HO113BPago` AS b ON ( b.HBHACtrl = a.HACtrl )
LEFT JOIN `HO113BRecibos` AS c ON ( c.HDHBCtrl = b.HBCtrl )
WHERE HAManzana = '03'
AND HALote = '09'
LIMIT 0 , 100
Paul Roub
  • 36,322
  • 27
  • 84
  • 93
Juan Manuel Alcorta Villarreal
  • 1
  • 1