miniu Posted April 24, 2010 Share Posted April 24, 2010 I have a problem, when I try to lunch this file I receive an information: 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 I don't know where should I change the SQL_BIG_SELECTS because they are not defined anywhere, here is the code: <?php include("connect.php"); include("admin.session.php"); $LeftLinkSection = 1; $pagetitle="Assigned Games"; if(isset($_GET['did'])) { $sql="delete from games where id=".$_GET['did']; $res=mysql_query($sql); echo mysql_error(); header('location:message.php?mid=3'); } ?> <HTML> <HEAD><title><?=Site_Title; ?></title> <SCRIPT language="javascript" src="body.js"></SCRIPT> <META http-equiv=Content-Type content="text/html; charset=utf-8"> <META content="MSHTML 6.00.2600.0" name=GENERATOR> <link rel="stylesheet" href="main.css" type="text/css"> <style type="text/css"> <!-- .style3 {color: #FFFFFF} --> </style> </HEAD> <BODY leftMargin=0 topMargin=0 marginheight="0" marginwidth="0"> <TABLE cellSpacing=0 cellPadding=0 width="100%" border=0 class="th-a"> <TR><TD height=60 valign="top" colspan="2" bgColor="#ffffff"><? include("top.php") ?></td></TR> <tr> <td width="20%" valign="top" bgcolor="#1c6995"><? include("left.php"); ?></td> <td width="80%" valign="top" bgcolor="#FFFFFF" height="480"> <TABLE width="97%" border=0 cellPadding=0 cellSpacing=0 align="center"> <TR><TD width="100%" class="H1"> </TD></TR> <TR><TD align="center" width="100%" class="a-l"><?php echo $Message2 ; ?></TD></TR> <TR> <? if($msg!="") { ?> <TR><TD align="center" width="100%" class="a-l"><font color="red"><b>User Deleted Successfully</b></font></TD></TR> <TR> <? } ?> <TD width="100%" class="H1">Manage <?=$pagetitle?></TD> </TR> <TR><TD background="images/vdots.gif"><IMG height=1 src="images/spacer.gif" width=1 border=0></TD></TR> <tr> <TD> <FORM name="order" action="#" method="post"> <TABLE cellSpacing=0 cellPadding=1 border=0 > <tr><td colspan="25" height="20"><b>View By Division Name</b></td></tr> <?=$prs_pageing->order();?> </TABLE> </form> <? if($_GET["order"]) { $order=$_GET["order"]; $strQueryPerPage="select ag.main_ref_accept,ag.ast_ref1_accept,ag.ast_ref2_accept,ag.id as agid,g.id,g.gid,d.dname,g.gdate,g.gtime,ht.tname as htname,at.tname as atname from games g inner join divisions d on g.div_id=d.id inner join teams ht on g.home_team_id=ht.id inner join teams at on g.away_team_id=at.id inner join assigned_games ag on g.gid=ag.gid where (main_ref_id=".$_SESSION["UsErId"]." or ast_ref_id1=".$_SESSION["UsErId"]." or ast_ref_id2=".$_SESSION["UsErId"].") and d.dname like '$order%'"; } else $strQueryPerPage="select ag.main_ref_accept,ag.ast_ref1_accept,ag.ast_ref2_accept,ag.id as agid,g.id,g.gid,d.dname,g.gdate,g.gtime,ht.tname as htname,at.tname as atname from games g inner join divisions d on g.div_id=d.id inner join teams ht on g.home_team_id=ht.id inner join teams at on g.away_team_id=at.id inner join assigned_games ag on g.gid=ag.gid where main_ref_id=".$_SESSION["UsErId"]." or ast_ref_id1=".$_SESSION["UsErId"]." or ast_ref_id2=".$_SESSION["UsErId"]; if(isset($_GET['sort'])) $strQueryPerPage.=" order by ".$_GET['sort']." ".$_GET['ordby']; else $strQueryPerPage.=" order by g.id desc"; //echo $strQueryPerPage; $strResultPerPage=mysql_query($strQueryPerPage); $strTotalPerPage=mysql_affected_rows(); ?> <div align="center"> <script type="text/javascript"><!-- google_ad_client = "pub-4703789267650596"; /* 728x90, utworzono 10-03-10 */ google_ad_slot = "0969661517"; google_ad_width = 728; google_ad_height = 90; //--> </script> <script type="text/javascript" src="http://pagead2.googlesyndication.com/pagead/show_ads.js"> </script> <script type="text/javascript"><!-- google_ad_client = "pub-4703789267650596"; /* 728x15, utworzono 09-12-14 */ google_ad_slot = "0676154885"; google_ad_width = 728; google_ad_height = 15; //--> </script> <script type="text/javascript" src="http://pagead2.googlesyndication.com/pagead/show_ads.js"> </script> </div> <FORM name="form2" action="#" method="post"> <TABLE width="100%" height="89" border=1 cellSpacing=0 class=t-a> <!--DWLayoutTable--> <TBODY> <TR class="th-a"> <TD colspan="9"> <? $result=$prs_pageing->number_pageing($strQueryPerPage,15,7); ?> </TD> </TR> <TR class=th-a> <TD width="8%" align="left"><a href="manage_games.php?sort=g.gdate&<? if($_GET['ordby']=='asc' || $_GET['ordby']=='' ) {?>ordby=desc<? } else {?>ordby=asc<? } ?>" class="link2">Game Date</a></TD> <TD width="12%" align="left"><a href="assigned_games.php?sort=d.dname&<? if($_GET['ordby']=='asc' || $_GET['ordby']=='' ) {?>ordby=desc<? } else {?>ordby=asc<? } ?>" class="link2">Division Name</a></TD> <TD width="8%" align="left"><a href="manage_games.php?sort=g.gtime&<? if($_GET['ordby']=='asc' || $_GET['ordby']=='' ) {?>ordby=desc<? } else {?>ordby=asc<? } ?>" class="link2">Game Time</a></TD> <TD width="11%" align="left"><a href="assigned_games.php?sort=ht.tname&<? if($_GET['ordby']=='asc' || $_GET['ordby']=='' ) {?>ordby=desc<? } else {?>ordby=asc<? } ?>" class="link2">Home Team Name</a></TD> <TD width="11%" align="left"><a href="assigned_games.php?sort=at.tname&<? if($_GET['ordby']=='asc' || $_GET['ordby']=='' ) {?>ordby=desc<? } else {?>ordby=asc<? } ?>" class="link2">Away Team Name</a></TD> <TD width="11%" align="left"><a href="assigned_games.php?sort=ag.main_ref_accept&<? if($_GET['ordby']=='asc' || $_GET['ordby']=='' ) {?>ordby=desc<? } else {?>ordby=asc<? } ?>" class="link2">Main Referee</a></TD> <TD width="11%" align="left"><a href="assigned_games.php?sort=ag.ast_ref1_accept&<? if($_GET['ordby']=='asc' || $_GET['ordby']=='' ) {?>ordby=desc<? } else {?>ordby=asc<? } ?>" class="link2">Assistant Referee 1</a></TD> <TD width="11%" align="left"><a href="assigned_games.php?sort=ag.ast_ref2_accept&<? if($_GET['ordby']=='asc' || $_GET['ordby']=='' ) {?>ordby=desc<? } else {?>ordby=asc<? } ?>" class="link2">Assistant Referee 2</a></TD> <TD width="16%" align="center"><span class="style3">Option</span></TD> </TR> <?php $tr="#f4f4f4"; $clr=1; $result=mysql_query($strQueryPerPage) or die(mysql_error()); while($row = mysql_fetch_object($result)) { $sql_main_referee="select fname,lname from referees,assigned_games where assigned_games.id=".$row->agid." and assigned_games.main_ref_id=referees.id;"; $rs_main=mysql_query($sql_main_referee) or die(mysql_error()); $row_main=mysql_fetch_object($rs_main); $sql_sub1_referee="select fname,lname from referees,assigned_games where assigned_games.id=".$row->agid." and assigned_games.ast_ref_id1=referees.id;;"; $rs_sub1=mysql_query($sql_sub1_referee) or die(mysql_error()); $row_sub1=mysql_fetch_object($rs_sub1); $sql_sub2_referee="select fname,lname from referees,assigned_games where assigned_games.id=".$row->agid." and assigned_games.ast_ref_id2=referees.id;;"; $rs_sub2=mysql_query($sql_sub2_referee) or die(mysql_error()); $row_sub2=mysql_fetch_object($rs_sub2); if($tr=="#FFFFFF") { $tr="#efefef"; $col="#000000"; } else { $tr="#FFFFFF"; $col="#000000"; } $tmpid = $row->id ; $tmpid = ""; ?> <tr bgcolor="<? echo $tr; ?>"> <TD align="left"><font color="<?=$col?>"> <? $txtgdate=$row->gdate; $txtgdate_arr=explode("-",$txtgdate); $txtgdate=$txtgdate_arr[1].'-'.$txtgdate_arr[2].'-'.$txtgdate_arr[0]; ?> <? echo stripslashes($txtgdate); ?></font></TD> <TD align="left"><font color="<?=$col?>"><? echo stripslashes($row->dname); ?></font></TD> <TD align="left"><font color="<?=$col?>"> <? $txtgtime=$row->gtime; $txtgtime_arr=explode("-",$txtgtime); $txtgtime=$txtgtime_arr[1].''.$txtgtime_arr[2].''.$txtgtime_arr[0]; ?> <? echo stripslashes($txtgtime); ?></font></TD> <TD align="left"><font color="<?=$col?>"><? echo stripslashes($row->htname); ?></font></TD> <TD align="left"><font color="<?=$col?>"><? echo stripslashes($row->atname); ?></font></TD> <TD align="left"><font color="<?=$col?>"><? echo stripslashes($row_main->fname).' '.stripslashes($row_main->lname); ?><br><? if($row->main_ref_accept=='P') echo "<font color='#FF9900'><strong>Pending</strong></font>"; if($row->main_ref_accept=='A') echo "<font color='#006633'><strong>Accepted</strong></font>"; if($row->main_ref_accept=='R') echo "<font color='#FF0000'><strong>Rejected</strong></font>"; ?></font></TD> <TD align="left"><font color="<?=$col?>"><? echo stripslashes($row_sub1->fname).' '.stripslashes($row_sub1->lname); ?></font><br><? if($row->ast_ref1_accept=='P') echo "<font color='#FF9900'><strong>Pending</strong></font>"; if($row->ast_ref1_accept=='A') echo "<font color='#006633'><strong>Accepted</strong></font>"; if($row->ast_ref1_accept=='R') echo "<font color='#FF0000'><strong>Rejected</strong></font>"; ?></font></TD> <TD align="left"><font color="<?=$col?>"><? echo stripslashes($row_sub2->fname).' '.stripslashes($row_sub2->lname); ?></font><br><? if($row->ast_ref2_accept=='P') echo "<font color='#FF9900'><strong>Pending</strong></font>"; if($row->ast_ref2_accept=='A') echo "<font color='#006633'><strong>Accepted</strong></font>"; if($row->ast_ref2_accept=='R') echo "<font color='#FF0000'><strong>Rejected</strong></font>"; ?></font></TD> <TD align="center"><input name="button" type="button" class="bttn-s" onClick="location.href='view_assigned_game.php?id=<?php echo $row->agid; ?>'" value="View"> </TD> </TR> <? $parentary = NULL ; } ?> <input type=hidden name="total" value="<? echo $strTotalNewRow; ?>"> <TR align=center> <TD colSpan=8> <? if(!$strTotalPerPage) { ?> <strong>No Game Detail to Display</strong> <? } ?> </TD> </TR> <TR align=center> <TD colSpan=10> </TD> </TR> </TABLE> </FORM> </TD> </TR> </TABLE> </td> </tr> </TABLE> </BODY></HTML> Quote Link to comment https://forums.phpfreaks.com/topic/199588-the-select-would-examine-more-than-max_join_size-rows/ Share on other sites More sharing options...
Mchl Posted April 24, 2010 Share Posted April 24, 2010 http://dev.mysql.com/doc/refman/5.0/en/server-session-variables.html#sysvar_sql_big_selects Just run mysql_query('SET SQL_BIG_SELECTS=1'); somewhere before the query that throws this error. Quote Link to comment https://forums.phpfreaks.com/topic/199588-the-select-would-examine-more-than-max_join_size-rows/#findComment-1047633 Share on other sites More sharing options...
miniu Posted April 24, 2010 Author Share Posted April 24, 2010 I have added it before the query on the beginning of the page if(isset($_GET['did'])) { but still the same message. Quote Link to comment https://forums.phpfreaks.com/topic/199588-the-select-would-examine-more-than-max_join_size-rows/#findComment-1047644 Share on other sites More sharing options...
DavidAM Posted April 24, 2010 Share Posted April 24, 2010 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 1) Did you read the error message and do what it says? You'll also want to check the JOINS The implication here is that you are trying to run a query that is going to have to process and lot of rows; maybe you left out a JOIN or WHERE condition causing a cartesian product. If you echo the query does it look right? Can you run it from the control panel? Do you get the results you expect. I looked at the big query (the first two in the code), and the only problem I see is that you are using "at" as a table alias; my editor flags that as a SQL keyword, is that causing a problem? The last three selects (referee names) look OK as well. You chose not to use a JOIN there but you have the tables joined in the WHERE clause. Without knowing the layout of your tables and the number of rows in each, I don't see a problem with the queries. But I would recommend double checking them before you circumvent a protective measure that could result in some long running queries. Also check "admin.session.php" to see if there are any SELECT statements there. Since we really don't know which SQL it is complaining about. If it turns out that all the SELECTs are fine, then you may want to review your indexing on the tables. Quote Link to comment https://forums.phpfreaks.com/topic/199588-the-select-would-examine-more-than-max_join_size-rows/#findComment-1047748 Share on other sites More sharing options...
Mchl Posted April 24, 2010 Share Posted April 24, 2010 Out of curiosity. What does SELECT @@SQL_MAX_JOIN_SIZE say? Quote Link to comment https://forums.phpfreaks.com/topic/199588-the-select-would-examine-more-than-max_join_size-rows/#findComment-1047764 Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.