up vote 0 down vote favorite

I have a query which works just like it's supposed to, however, I want to optimize it to use less queries.

The following code is from a user medals plugin I'm creating for a bulletin board system.

$types = array('numposts', 'numthreads', 'numreps');
$medals = "";

foreach($types as $type)
{
    $query = $db->query("
        SELECT u.*, m.* 
        FROM ".TABLE_PREFIX."medals_".$type." m
        LEFT JOIN ".TABLE_PREFIX."medals u ON (m.".$type."_id=u.awid)
        WHERE awuid='".intval($post['uid'])."' AND type='".$type."'
        ORDER BY `".$type."` DESC LIMIT 1
    "); 

    while($results = $db->fetch_array($query))
    {
        $medals .= "<img src=\"uploads/medals/".$results['icon']."\" border=\"0\" alt=\"".$results['descrition']."\" title=\"".$results['description']."\" /> ";
    }
}

For each post, it runs this query to display the user's medals. Depending on the number of posts, the query is multiplied times three. So, for example if a thread has 10 posts...10x3 is 30...that's 30 queries being executed by just this code.

What's the best way to optimize this to use less queries? Perhaps caching? I'm not too good at optimization, so I'm asking for your input here.

share|improve this question
73% accept rate
Since "number of posts" is the more serious multiplier, shouldn't we see that code as well? – webbiedave Aug 15 '11 at 17:07
feedback

1 Answer

up vote 1 down vote

Link in the posts table, so you can fetch medals only for those users whose posts are actually being displayed. If you run this query separately, you'd get only 1 medals list record per user, which you can store in an array or other variable.

When you then start displaying the posts, you can refer back to the medals list and pull up the user's medals. If (say) your 10 posts had only 2 people chatting, this'd cost you a single query that fetches 2x3=6 rows of data (2 users x 3 types of medals), and some memory to store the medals results while you actually fetch/display the posts.

share|improve this answer
This is just a plugin...the posts table is already linked in via the bulletin board's core code. And it's only fetching medals of the users whose posts are being displayed, however it's executing this query for each post. I want to somehow only execute this query once per user who is part of the thread. – Spencer Aug 15 '11 at 17:12
If you can't move the code upwards and attach it to the post-fetching code, then use a static var to store user IDs and their medal data. only execute the query if a user's ID isn't inside that static array. if it is, return the array data. otherwise fetch the data, store it in the array, then return the array data anyways. – Marc B Aug 15 '11 at 17:13
Could you expound a bit more, please? Perhaps provide an example code. – Spencer Aug 15 '11 at 17:17
Example 4+: php.net/manual/en/language.variables.scope.php – Marc B Aug 15 '11 at 17:20
feedback

Your Answer

 
or
required, but never shown
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.