PHP/MySQL Query Optimization?

Question

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.

Since "number of posts" is the more serious multiplier, shouldn't we see that code as well? — webbiedave
– webbiedave, Commented Aug 15, 2011 at 17:07

Marc B · Accepted Answer · 2011-08-15 17:06:44Z

1

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.

answered Aug 15, 2011 at 17:06

Marc B

362k44 gold badges433 silver badges508 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Spencer Over a year ago

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.

Marc B Over a year ago

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.

Spencer Over a year ago

Could you expound a bit more, please? Perhaps provide an example code.

Collectives™ on Stack Overflow

PHP/MySQL Query Optimization?

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related