CODEWITHSUNDEEP
mysqlperformance
RSilva
RSilva

Reputation: 6943

MySQL: Most efficient way to count records added today

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

Upvotes: 2

Views: 2737

Answers (2)

Greg
Greg

Reputation: 321688

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 CONCAT(CURDATE(), ' ', '00:00:00') AND CONCAT(CURDATE(), ' ', '23:59:59');

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

Upvotes: 4

Wookai
Wookai

Reputation: 21733

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 !

Upvotes: 2

Related Questions