MySQL: Most efficient way to count records added today

Question

This is the MySQL table structure that I have:

itemID (BIGINT)
userID (BIGINT)
wasAdded (DATETIME)

I need to get the number of items introduced in the current day, by the X userID

What would be the most efficient way to do this?

Thanks

Answer 1

The best way would be to add an index that covers userID and wasAdded, then you can do

SELECT COUNT(*) FROM my_table WHERE userID = 'X' AND wasAdded BETWEEN '2008-01-01 00:00:00' AND '2008-01-01 23:59:59';

This shouldn't even need to read from the table, as all the data it needs is in the index.

Answer 2

This should do the trick :

SELECT COUNT(itemID) FROM 'tableName' WHERE TO_DAYS(wasAdded) = TO_DAYS(NOW()) AND userID = X

However, make sure to have an index on wasAdded and userID !

Answer 3

SELECT userId, COUNT(*) 
FROM my_table
WHERE '2008-01-01' <= wasAdded AND wasAdded < '2008-01-01' + 1
GROUP BY userId