Optimizing MySQL operation to get counts

Question

I have this lovely piece of code that copies out different counts of rows so I can build an associative list of how many of each good a citizen possesses.

$citizen_id = 1;
$goods = array();
$goods_result = mysql_query("SELECT id,name FROM goods");
while(($goods_row = mysql_fetch_assoc($goods_result))) {
    $good_id = $goods_row['id'];
    $query = "SELECT count(*) FROM possessions where citizen_id=$citizen_id and good_id=$good_id";
    $possessed_result = mysql_query($query);
    $possessed_row = mysql_fetch_row($possessed_result);
    if($possessed_row) {
        $possessed = $possessed_row[0];
        $goods[$goods_row['name']] = $possessed;
    }
}
echo json_encode ($goods);

It works, but it runs too slow. It seems to me that there must be a way to get MySQL to build a table of counts and return it to me in a single query, but I have no idea how to figure out how to do that. Any ideas on how to make this operation faster?

Adriaan Stander · Accepted Answer · 2014-02-17 18:41:47Z

2

How about using a query like

SELECT  g.id good_id,
        count(*) cnt_of_goods
FROM    possessions p INNER JOIN
        goods g ON  p.good_id = g.id
where   citizen_id=$citizen_id 
GROUP BY    g.id

answered Feb 17, 2014 at 18:41

Adriaan Stander

167k32 gold badges294 silver badges288 bronze badges

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

Collectives™ on Stack Overflow

Optimizing MySQL operation to get counts

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related