2. PHPRunner General questions
  3. really need some urgent help on this one
This topic is locked

really need some urgent help on this one

 		2/24/2009 3:29:36 PM
PHPRunner General questions
G
 garethp authorDevClub member

Hi
I am hoping someone can help quickly as one of my main website pages is currently down.
I have a complicated sql statement and tables with large numbers of items.
Therefore I am now (worked Ok yesterday but guess the table has got bigger) getting the error 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


It seems obvious I need to add the statement SET SQL_BIG_SELECTS=1 or something similar but I jsut do not know where to add it. I am guess somewhere in the page_variables.php but not really sure. I ahve tried everything I can think and searched the web but with no luck.
HELP!!!!
D
 		Denton 2/24/2009

Hi

I am hoping someone can help quickly as one of my main website pages is currently down.
I have a complicated sql statement and tables with large numbers of items.
Therefore I am now (worked Ok yesterday but guess the table has got bigger) getting the error message
It seems obvious I need to add the statement SET SQL_BIG_SELECTS=1 or something similar but I jsut do not know where to add it. I am guess somewhere in the page_variables.php but not really sure. I ahve tried everything I can think and searched the web but with no luck.
HELP!!!!


Who designed the website and who wrote the original quesry?
Denton
G
 		garethp authorDevClub member 2/24/2009

Denton.
Thanks for posting - I did using php runner. I had coded the sql statement manually. I needed to pull data from a number of independantly linked to one master table and had to use outer joins so it is a bit complicated. All are indexed.
Gareth
S
 		swanside 2/24/2009

If you did it in PHPRunner, is it not still in the project you made it in?

If not, is it in the output folder before you uploaded it?
Sergey Kornilov admin 2/24/2009

Usually this means a problem with SQL query that needs to be re-written.
Hopefully this helps:

http://bytes.com/groups/mysql/428423-set-sql_big_selects-1-a
G
 		garethp authorDevClub member 2/25/2009

Thanks all for taking time to respond so quickly on this one. I will have a look at re-writing the query. However for the time being I was hoping I could add SET SQL_BIG_SELECTS=1 somewhere.
In the thread posted by admin it stated..
It's a separate statement, which you can execute at any time before you

execute your SELECT.

SET SESSION SQL_BIG_SELECTS=1;

SELECT C.CourseID, ... <the rest of the query>;
So I have tried adding SET SESSION SQL_BIG_SELECTS=1; in the phprunner edit sql page at the top before the select but get an error.
Can I add this somewhere to make it work again for now - the query ws working fine but I guess the number of records has now got too big.
Thanks
Gaeth
Sergey Kornilov admin 2/25/2009

You can use BeforeSQLQuery event and the following code:

global $conn;

db_exec("SET SESSION SQL_BIG_SELECTS=1;",$conn);