MAX_JOIN_SIZE

I have been testing an earlier Mysql bug and now I know the reason,
but not how to fix it.

The following code

SELECT *
FROM ml_lopp
LEFT JOIN scfmforening ON ( scfmforening.scfmnum = ml_lopp.scfmnum )
LEFT JOIN ml_newtidplats ON ( ml_newtidplats.loppnum =
ml_lopp.loppnum )
LEFT JOIN ml_mainkon ON ( ml_mainkon.loppnum = ml_lopp.loppnum )
LEFT JOIN ml_anm ON ( ml_anm.loppnum = ml_lopp.loppnum )
LEFT JOIN ml_avg ON ( ml_avg.loppnum = ml_lopp.loppnum )
LEFT JOIN ml_dist ON ( ml_dist.loppnum = ml_lopp.loppnum )
LEFT JOIN ml_eft ON ( ml_eft.loppnum = ml_lopp.loppnum )
LEFT JOIN ml_upply ON ( ml_upply.loppnum = ml_lopp.loppnum )
LEFT JOIN ml_konpers ON ( ml_konpers.loppnum = ml_lopp.loppnum )
LEFT JOIN ml_konsem ON ( ml_konsem.loppnum = ml_lopp.loppnum )
WHERE ml_lopp.sank2006 = "ja"
ORDER BY date1, ml_lopp.loppnum
LIMIT 0 , 30

Gives me this error
#1104 - The SELECT would examine more rows than MAX_JOIN_SIZE. Check
your WHERE and use SET SQL_BIG_SELECTS=1 or SET SQL_MAX_JOIN_SIZE=# if
the SELECT is ok

So where do I use "SET SQL_BIG_SELECTS=1" (I am entering this code
from a php call)

Using the command "show variables" in a mysql query window directly in
the database shows max_join_size as 33554432. ! But how can I change
this? The chances are it is coded by my web hotel supplier (one.com)
and is not a user changable variable. So can I set this from a php
call to mysql or is there a workaround?

The actual php string that does not work is
$ml_collect='SELECT * FROM ml_lopp LEFT JOIN scfmforening ON
(scfmforening.scfmnum=ml_lopp.scfmnum) LEFT JOIN ml_newtidplats ON
(ml_newtidplats.loppnum=ml_lopp.loppnum) LEFT JOIN ml_mainkon ON
(ml_mainkon.loppnum=ml_lopp.loppnum) LEFT JOIN ml_anm ON
(ml_anm.loppnum=ml_lopp.loppnum) LEFT JOIN ml_avg ON
(ml_avg.loppnum=ml_lopp.loppnum) LEFT JOIN ml_dist ON
(ml_dist.loppnum=ml_lopp.loppnum) LEFT JOIN ml_eft ON
(ml_eft.loppnum=ml_lopp.loppnum) LEFT JOIN ml_upply ON
(ml_upply.loppnum=ml_lopp.loppnum) LEFT JOIN ml_konpers ON
(ml_konpers.loppnum=ml_lopp.loppnum) LEFT JOIN ml_konsem ON
(ml_konsem.loppnum=ml_lopp.loppnum) WHERE ml_lopp.sank2006="ja" ORDER
BY date1, ml_lopp.loppnum';

Followed by

$ml_upg=mysql_query($ml_collect);
$num_ml=mysql_numrows($ml_upg);
mysql_close();

This does not give the 1104 error, it simply returns no data. Is there
a way of using "SET SQL_BIG_SELECTS=1" while the code runs? Or any
other workaround?

Thanks in advance, very greatfull
Garry Jones
Sweden

GarryJones schrieb:
> I have been testing an earlier Mysql bug and now I know the reason,
> but not how to fix it.
>
> The following code
>
> SELECT *
> FROM ml_lopp
> LEFT JOIN scfmforening ON ( scfmforening.scfmnum = ml_lopp.scfmnum )
> LEFT JOIN ml_newtidplats ON ( ml_newtidplats.loppnum =
> ml_lopp.loppnum )
> LEFT JOIN ml_mainkon ON ( ml_mainkon.loppnum = ml_lopp.loppnum )
> LEFT JOIN ml_anm ON ( ml_anm.loppnum = ml_lopp.loppnum )
> LEFT JOIN ml_avg ON ( ml_avg.loppnum = ml_lopp.loppnum )
> LEFT JOIN ml_dist ON ( ml_dist.loppnum = ml_lopp.loppnum )
> LEFT JOIN ml_eft ON ( ml_eft.loppnum = ml_lopp.loppnum )
> LEFT JOIN ml_upply ON ( ml_upply.loppnum = ml_lopp.loppnum )
> LEFT JOIN ml_konpers ON ( ml_konpers.loppnum = ml_lopp.loppnum )
> LEFT JOIN ml_konsem ON ( ml_konsem.loppnum = ml_lopp.loppnum )
> WHERE ml_lopp.sank2006 = "ja"
> ORDER BY date1, ml_lopp.loppnum
> LIMIT 0 , 30
>
> Gives me this error
> #1104 - The SELECT would examine more rows than MAX_JOIN_SIZE. Check
> your WHERE and use SET SQL_BIG_SELECTS=1 or SET SQL_MAX_JOIN_SIZE=# if
> the SELECT is ok
>
> So where do I use "SET SQL_BIG_SELECTS=1" (I am entering this code
> from a php call)
>
> Using the command "show variables" in a mysql query window directly in
> the database shows max_join_size as 33554432. ! But how can I change
> this? The chances are it is coded by my web hotel supplier (one.com)
> and is not a user changable variable. So can I set this from a php
> call to mysql or is there a workaround?
>
> The actual php string that does not work is
> $ml_collect='SELECT * FROM ml_lopp LEFT JOIN scfmforening ON
> (scfmforening.scfmnum=ml_lopp.scfmnum) LEFT JOIN ml_newtidplats ON
> (ml_newtidplats.loppnum=ml_lopp.loppnum) LEFT JOIN ml_mainkon ON
> (ml_mainkon.loppnum=ml_lopp.loppnum) LEFT JOIN ml_anm ON
> (ml_anm.loppnum=ml_lopp.loppnum) LEFT JOIN ml_avg ON
> (ml_avg.loppnum=ml_lopp.loppnum) LEFT JOIN ml_dist ON
> (ml_dist.loppnum=ml_lopp.loppnum) LEFT JOIN ml_eft ON
> (ml_eft.loppnum=ml_lopp.loppnum) LEFT JOIN ml_upply ON
> (ml_upply.loppnum=ml_lopp.loppnum) LEFT JOIN ml_konpers ON
> (ml_konpers.loppnum=ml_lopp.loppnum) LEFT JOIN ml_konsem ON
> (ml_konsem.loppnum=ml_lopp.loppnum) WHERE ml_lopp.sank2006="ja" ORDER
> BY date1, ml_lopp.loppnum';
>
> Followed by
>
> $ml_upg=mysql_query($ml_collect);
> $num_ml=mysql_numrows($ml_upg);
> mysql_close();
>
> This does not give the 1104 error, it simply returns no data. Is there
> a way of using "SET SQL_BIG_SELECTS=1" while the code runs? Or any
> other workaround?
>
> Thanks in advance, very greatfull
> Garry Jones
> Sweden
>
$setStmt='SET SQL_BIG_SELECTS=1';
$mq = mysql_query($setStmt);

should do it.