Mysql nested query optimization?

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 you 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.

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 doesn't return the users with 0 transactions , where the previous query did. – mrkmg Apr 27 at 19:47 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 Apr 27 at 19:52 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!

– ypercube Apr 27 at 19:55 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. – mrkmg Apr 27 at 20:12 @Kevin: What indexes do you have on ActivityLog?

– ypercube Apr 27 at 20:14.

Seem that is a defect of MySQL ... See here . This is a well-known problem (see bug #12106 for just one of examples): subquery is considered to correlated (wrongly) and thus is execured for each and every row from the outer query. The problem exist from the 2006 and currently is unresolved ...

I don't think that happens in the OP's case; the subquery is really correlated. With appropriate indexes, the GROUP BY will probably choose a much better plan – MarkR Apr 27 at 21:44.

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.

I cant really gove you an answer,but what I can give you is a way to a solution, that is you have to find the anglde that you relate to or peaks your interest. A good paper is one that people get drawn into because it reaches them ln some way.As for me WW11 to me, I think of the holocaust and the effect it had on the survivors, their families and those who stood by and did nothing until it was too late.