code_junkie

MySQL: Most efficient way to count records added today

Nov 13th, 2011
173
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
text 0.39 KB | None | 0 0
  1. itemID (BIGINT)
  2. userID (BIGINT)
  3. wasAdded (DATETIME)
  4.  
  5. SELECT COUNT(*) FROM my_table WHERE userID = 'X' AND wasAdded BETWEEN '2008-01-01 00:00:00' AND '2008-01-01 23:59:59';
  6.  
  7. SELECT COUNT(itemID) FROM 'tableName' WHERE TO_DAYS(wasAdded) = TO_DAYS(NOW()) AND userID = X
  8.  
  9. SELECT userId, COUNT(*)
  10. FROM my_table
  11. WHERE '2008-01-01' <= wasAdded AND wasAdded < '2008-01-01' + 1
  12. GROUP BY userId
Add Comment
Please, Sign In to add comment