Effienciecy

[Isomerizer]

For the last few hours I've been going through my game trying to make the mysql queries efficient.

Now I've come down the v1 hall of fame, and i realise this kills my server.

"This page loaded in 15.25 seconds."

For total stats and best strength, defense etc.

The query used for total stats is:

$q=mysql_query("SELECT u.username, u.userid, g.gangPREF FROM `users` u LEFT JOIN userstats us ON
u.userid=us.userid LEFT JOIN gangs g ON g.gangID=u.gang ORDER BY 
(us.might+us.stealth+us.defence+us.force+us.IQ) DESC, u.userid ASC LIMIT 20", $c);

 

I then fetch it using mysql_fetch_array();.

Any idea how i could make this query more efficient? I'm selecting only the columns i'll need (i think).

Also, if you have any other tips on MySQL efficiencies i would be grateful :).

Thanks.

[Floydian]

Re: Effienciecy

$q=mysql_query("SELECT u.username, u.userid, g.gangPREF FROM `users` u

LEFT JOIN userstats us ON u.userid=us.userid

LEFT JOIN gangs g ON g.gangID=u.gang

ORDER BY (us.might+us.stealth+us.defence+us.force+us.IQ) DESC, u.userid ASC LIMIT 20", $c);

You could take that query and try this:

$q=mysql_query("SELECT u.username, u.userid, g.gangPREF FROM `users` u

LEFT JOIN gangs g ON g.gangID=u.gang

order by (select (us.might+us.stealth+us.defence+us.force+us.IQ) from userstats as us where u.userid = us.userid) DESC, u.userid ASC LIMIT 20", $c);

I'm not guaranteeing that that will be faster, but it's another way to do the same thing.

Another thing you can do is put an index on columns you use to match things with.

u.gang would be a good one.