Mysql nested query optimization

I have a table that logs variou开发者_高级运维s transactions for a CMS. It logs the username, action, and time. I have made the following query to tell me how many transactions each user made in the past two days, but it is so slow its faster for me to send a bunch of separate querys at this point. Am I missing a fundamental rule for writing nested queries?

SELECT DISTINCT
    `username`
   , ( SELECT COUNT(*)
       FROM `ActivityLog`
       WHERE `username`=`top`.`username`
         AND `time` > CURRENT_TIMESTAMP - INTERVAL 2 DAY
     ) as `count`
FROM `ActivityLog` as `top`
WHERE 1;

---

You could use:

   SELECT username
        , COUNT(*) AS count
   FROM ActivityLog
   WHERE time > CURRENT_TIMESTAMP - INTERVAL 2 DAY
   GROUP BY username

An index on (username, time) would be helpful regarding speed.

---

If you want users with 0 transcations (the last 2 days), use this:

SELECT DISTINCT
    act.username
  , COALESCE(grp.cnt, 0) AS cnt
FROM ActivityLog act
  LEFT JOIN
    ( SELECT username
           , COUNT(*) AS count
      FROM ActivityLog
      WHERE time > CURRENT_TIMESTAMP - INTERVAL 2 DAY
      GROUP BY username
    ) AS grp
  ON grp.username = act.username 

or, if you have a users table:

SELECT 
    u.username
  , COALESCE(grp.cnt, 0) AS cnt
FROM users u
  LEFT JOIN
    ( SELECT username
           , COUNT(*) AS count
      FROM ActivityLog
      WHERE time > CURRENT_TIMESTAMP - INTERVAL 2 DAY
      GROUP BY username
    ) AS grp
  ON grp.username = u.username 

Another way, similar to yours, would be:

   SELECT username
        , SUM(IF(time > CURRENT_TIMESTAMP - INTERVAL 2 DAY, 1, 0))
          AS count
   FROM ActivityLog
   GROUP BY username

or even this (because true=1 and false=0 for MySQL):

   SELECT username
        , SUM(time > CURRENT_TIMESTAMP - INTERVAL 2 DAY)
          AS count
   FROM ActivityLog
   GROUP BY username

---

No need for nesting...

SELECT `username`, COUNT(`username`) as `count` FROM `ActivityLog` WHERE `time` > CURRENT_TIMESTAMP - INTERVAL 2 DAY GROUP BY `username`

Also don't forget to add an INDEX on time if you want to make it even faster