userstats index

[Floydian]

I added a lil deal to a game with a query like this:

 

$q_get = sprintf('select u.userid, u.username, u.level from users as u
left join userstats as s on u.userid = s.userid
where u.location = %d
order by (s.strength + s.agility + s.guard) desc limit 3', $ir['location']);
$q_get = $db->query($q_get);

 

And it took the page load time from about 1 second to about 10 seconds.

Now, IMHO, that query isn't egregiously ill constructed, so it shouldn't completely destroy any performance the game was getting.

It turns out, that putting a Primary Key index on the `userid` column of the `userstats` table brought the query execution time down from about 9 seconds to 0.02 seconds.

mccodes userstats table doesn't come with an index on the userid column by default.

Hope this helps a few folks...