CODEWITHSUNDEEP
mysqloptimization
Luke
Luke

Reputation: 707

Optimizing MySQL operation to get counts

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?

Upvotes: 0

Views: 32

Answers (1)

Adriaan Stander
Adriaan Stander

Reputation: 166566

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

Upvotes: 2

Related Questions