Error Help Please!!!!

[chicka]

When players click on a users profile this is what they get.. I have not touched the files in a while so its nothing I did.. can someone please explain what this means and a fix if possible.. thanks in advance!!

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

Query was SELECT u.*,us.*,c.*,g.*,f.* FROM users u LEFT JOIN userstats us ON u.userid=us.userid LEFT JOIN cities c ON u.location=c.cityid LEFT JOIN gangs g ON g.gangID=u.gang LEFT JOIN fedjail f ON f.fed_userid=u.userid WHERE u.userid=9233

[a_bertrand]

Well basically it tells you all.

When running that query, too many joins are done and therefore could lead to a slow down / memory usage. For that MySQL doesn't allow it by default. Yet either you rewrite your query to make it work with smaller joins or... you need to modify (if possible) the my.cfg file to set the SET SQL_BIG_SELECTS=1 or SET SQL_MAX_JOIN_SIZE=#

Both of those changes might not be possible on a shared host and are quiet surely a dead road as it will make slow queries at the end.

Best solution would be to rewrite the query.

[zefiux]

Did you install game properly? And if you did, maybe you changed mySQL username/password after that?(I've no idea giving 1st idea what "shoot" in my head)

[chicka]

Did you install game properly? And if you did, maybe you changed mySQL username/password after that?(I've no idea giving 1st idea what "shoot" in my head)

Yes I installed my game properly about 3 years ago. No I didn't change any usernames or passwords.

a_bertrand thanks for the info.. I appreciate it

[Spudinski]

I don't know why you are querying for so much unwanted data.

Let's make it a little more simpler:

You're queries:

    (SELECT u.* FROM `users` WHERE u.userid = 1)
   (SELECT us.* FROM `userstats` WHERE us.userid = 1)
   (SELECT c.* FROM `cities` WHERE u.location = c.cityid)
   (SELECT g.* FROM `gangs` WHERE g.gangID=u.gang)
   (SELECT f.* FROM `fedjail` WHERE f.fed_userid=u.userid)

 

If you really need all the data, or don't know what to expect, I'd suggest these queries seperately:

SELECT     u.*, g.*, c.*
 FROM 
   `users` u 
 LEFT JOIN
   gangs g
 ON
   g.gangID = u.gang
 LEFT JOIN 
   cities c 
 ON 
   u.location = c.cityid
 WHERE 
   u.userid = 1

 

And:

SELECT    f.*, us.*
FROM
   fedjail f 
 LEFT JOIN
   userstats us 
 ON 
   fed_userid = us.userid 
WHERE 
  f.fed_userid= 1

 

Quickly tested, and seems to work, apart from the obvious that no userstats would should if no fedjail record is present for that specific ID.

Though, it's still not optimized in any way.

Edited February 14, 2012 by Spudinski

[chicka]

$q=$db->query("SELECT u.*,us.*,c.*,g.*,f.* FROM users u LEFT JOIN userstats us ON u.userid=us.userid LEFT JOIN cities c ON u.location=c.cityid LEFT JOIN gangs g ON g.gangID=u.gang LEFT JOIN fedjail f ON f.fed_userid=u.userid WHERE u.userid={$_GET['u']}");

if($db->num_rows($q) == 0)
{
print "Sorry, we could not find a user with that ID, check your source.";
}

 

Thats the query that is giving me a hard time all of a sudden. I tried the above code but didn't work.. If someone could lend a hand I would appreciate it

[bluegman991]

Could your host have possibly lowered the max join size?

[chicka]

Could your host have possibly lowered the max join size?

Yes I believe so what out warning. Pretty much nothing I can do except optimize the query. Well I tried that and failed :( was hoping someone on here could do it

[bluegman991]

What you can do is make 5 different queries selecting the needed fields.

Then fetch the array of each of them then merge the arrays.

 

$id=abs((int) $_GET['u']);
$q1="SELECT * FROM `users` WHERE `userid`=$id";
$q2=select next table blah blah;
...
$q5=select last table blah bla;

//run queries $q1=$db->query($q1) ... to $q5

//fetch each row and pump into array $q1rows[]=$db->fetch_row($q1)

$userdata=array_merge($q1rows,$q2rows,$q3rows,$q4rows,$q5rows);

Edited February 15, 2012 by bluegman991

[Spudinski]

What you can do is make 5 different queries selecting the needed fields.

Then fetch the array of each of them then merge the arrays.

 

$id=abs((int) $_GET['u']);
$q1="SELECT * FROM `users` WHERE `userid`=$id";
$q2=select next table blah blah;
...
$q5=select last table blah bla;

//run queries $q1=$db->query($q1) ... to $q5

//fetch each row and pump into array $q1rows[]=$db->fetch_row($q1)

$userdata=array_merge($q1rows,$q2rows,$q3rows,$q4rows,$q5rows);

If I may might a suggestion:

5 queries would be slower than the original. The JOIN method can still be kept, but it's usage can just be lowered upon.

Two, maybe three queries should suffice.

I would also like to point the OP into the right direction for learning about this:

Ref 1: http://dev.mysql.com/doc/refman/5.6/en/join.html

Ref 2: http://dev.mysql.com/doc/refman/5.6/en/union.html

Red 3: http://dev.mysql.com/doc/refman/5.6/en/select.html

[bluegman991]

That would work to. I didn't suggest a join because I have no idea what the max joins are for her host. It's probably safe to assume that max join is not less than 2.

Edited February 16, 2012 by bluegman991

[Spudinski]

That would work to. I didn't suggest a join because I have no idea what the max joins are for his host. It's probably safe to assume that max join is not less than 2.

Of course not, but it's rather weird that it's lower than five.

It's a recent misconfiguration if you ask me.

[chicka]

If I may might a suggestion:

5 queries would be slower than the original. The JOIN method can still be kept, but it's usage can just be lowered upon.

Two, maybe three queries should suffice.

I would also like to point the OP into the right direction for learning about this:

Ref 1: http://dev.mysql.com/doc/refman/5.6/en/join.html

Ref 2: http://dev.mysql.com/doc/refman/5.6/en/union.html

Red 3: http://dev.mysql.com/doc/refman/5.6/en/select.html

Thanks spudinski for the links. i'll look them over and see what I can come up with.

[chicka]

That would work to. I didn't suggest a join because I have no idea what the max joins are for his host. It's probably safe to assume that max join is not less than 2.

"her" not "his" just thought I would point that out LOL

[bluegman991]

Lol, sorry about that. Edited my post.

[chicka]

Lol, sorry about that. Edited my post.

LOL no problem

I have since had the problem fixed. I would like to say a big thank you to SomeRandomBastard for helping me out. I appreciate your help..