Mysql nested query optimization

Question

I have a table that logs various 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;

ypercubeᵀᴹ · Accepted Answer · 2011-04-27 20:08:56Z

2

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

edited Apr 27, 2011 at 20:08

answered Apr 27, 2011 at 19:42

ypercubeᵀᴹ

116k19 gold badges181 silver badges248 bronze badges

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

6 Comments

mrkmg Over a year ago

wow, thank you so much ypercube, that worked amazingly faster. Can you explain why that is faster? I am talking it took 34 sec previously, and now it took .09 sec. But, it doesnt return the users with 0 transactions , where the previous query did.

ypercubeᵀᴹ Over a year ago

You can run EXPLAIN yourquery and EXPLAIN myquery to see how MySQL "plans" to run the queries. I can make guesses but it really depends on the indexes you have on the table, its size and distibution of data.

ypercubeᵀᴹ Over a year ago

What is surely better, is that this is one query, and then a grouping. If you have an index on time, the WHERE check will be fast, in both queries. But in your case, MySQL may have to run a subquery (and scan the whole table) for every different username in the table!

mrkmg Over a year ago

Thank you VERY much. I do have a users table and I modified your query to utilize my schema. Its so fast, got .007 sec. I have never even heard of the COALESCE function. Thanks again.

ypercubeᵀᴹ Over a year ago

@Kevin: What indexes do you have on ActivityLog ?

|

Capsule · Accepted Answer · 2011-04-27 19:43:53Z

0

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

answered Apr 27, 2011 at 19:43

Capsule

6,1531 gold badge24 silver badges28 bronze badges

Collectives™ on Stack Overflow

Mysql nested query optimization

2 Answers 2

6 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

6 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related