Difficult MySQL Query

In our database tables we keep a number of counting columns to help reduce the number of complex look-up queries. For example, in our users table we have columns for the number of reviews written, photos uploaded, friends, followers, etc. To help make sure these stay in sync we have a script that runs periodically to check and update these counting columns.

I've been attempting to write an efficient query that calculates the number of friends a specific user has and haven't been able to figure out how to do it. In our friend model someone is a friend if they add you as a friend (no confirmation needed), so you have to count the number of unique of people when adding the number of friends you have added plus the number of people who have added you as a friend.

Here are two queries that each work to update the friend count for all the rows in our users table for friendship in a single direction. What I can't figure out it how to combine them so you get the total number of unique friends a user has:

Users Who Added You As A Friend

UPDATE users 
  JOIN (SELECT cid2, COUNT(*) as c 
         FROM connections 
         JOIN users ON connections.cid1 = users.user_id
        WHERE connection_type = "MM开发者_Go百科" 
          AND connections.status="A" 
          AND users.status != "D"
     GROUP BY cid2) f ON f.cid2 = users.user_id 
   SET users.friends = f.c
 WHERE users.status != "D";

Users Who You Added As A Friend

UPDATE users u 
  JOIN (SELECT cid1, COUNT(*) as c
          FROM connections 
          JOIN users ON connections.cid1 = users.user_id 
         WHERE connection_type = "MM" 
           AND connections.status = "A" 
           AND users.status != "D" 
      GROUP BY cid1) f ON f.cid1 = users.user_id 
   SET users.friends = f.c
 WHERE users.status != "D";

Attempt 3...

UPDATE
  users 
INNER JOIN
(
  SELECT
    connections.cid1  AS user_id,
    COUNT(*)          AS total
  FROM
  (
    SELECT cid1, cid2 FROM connections WHERE connection_type = 'MM' AND status = 'A'
    UNION
    SELECT cid2, cid1 FROM connections WHERE connection_type = 'MM' AND status = 'A'
  )
    AS connections
  INNER JOIN
    users
      ON  users.user_id = connections.cid2
      AND users.status != 'D'
  GROUP BY
    connections.cid1
)
  AS friends
    ON friends.user_id = users.user_id 
SET
  users.friends = friends.total
WHERE
  users.status != 'D';

(Other options deleted at OP's request. See edit history if interested.)

You could write it as a stored procedure which does both SELECT COUNT() and then adds it to a counter variable, then issue a single UPDATE

How important is the status flag? You're going to run into trouble trying to update the users table with a sub-query which references the same table.

Consider:

UPDATE connections SET status='D' WHERE cid1 IN 
(SELECT user.id
 FROM users u
 WHERE status='D');

UPDATE connections SET status='D' WHERE cid2 IN 
(SELECT user.id
 FROM users u
 WHERE status='D');

UPDATE users u
SET friends = (
  SELECT COUNT(DISTINCT friend)
  FROM (
   SELECT c1.cid2 as friend
   FROM connections c1
    WHERE c1.connection_type = 'MM' 
      AND c1.connections.status='A'
      AND c1.status<>'D'
      AND c1.cid1=u.id 
   UNION
   SELECT c2.cid1 AS friend
   FROM connections c2
    WHERE c2.connection_type = 'MM' 
      AND c2.connections.status='A'
      AND c2.status<>'D'
      AND c2.cid1=u.id 
   )
)