In our site we had a PHP script page which executes a MYSQL database query to fetch records and display report.
That page worked fine for few months, but since last few days it started giving an error message:
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
Now to solve the above error I searched the web and found a proper solution, which I would like to discuss here.
The error had occurred because in our MYSQL server SQL_BIG_SELECTS variable was turned off.
Some basic information about SQL_BIG_SELECTS variable is as under:
1. SQL_BIG_SELECTS variable prevents users from accidentally executing unnecessary large queries.
2. MYSQL determines whether or not a query is large based on the value of MAX_JOIN_SIZE variable. If the query is examining more than this number of rows, it will consider it as a big select or a large query. We can execute SHOW VARIABLES command to view values of MYSQL server variables.
3. I believe that indexing and good WHERE clause can prevent this problem.
Now if we want to turn on the SQL_BIG_SELECTS variable then we can do it from my.cnf file or at server startup. But some VPS (Virtual Private Server) servers do not allow us to do that.
So as an alternate solution I executed
mysql_query("SET OPTION SQL_BIG_SELECTS = 1");
query in our PHP script just after the database connection, and it works fine.
The solution works fine. But this could be said a temporary solution, because some servers don’t provide enough permissions. If the sever provides it you can change the my.cnf file or at server startup and implement it permanently.
Otherwise implement the above given solution. It remains in effect during the current database session between our PHP application and the database. So we have to execute above query after each database connection.
Hope this will help you.