Jump to content
MakeWebGames

Recommended Posts

Posted

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

Posted

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.

Posted
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

Posted (edited)

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 by Spudinski
Posted
$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

Posted
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

Posted (edited)

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 by bluegman991
Posted
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

Posted (edited)

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 by bluegman991
Posted
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.

Posted
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.

Posted
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

Posted
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..

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...