Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

It took me quite a while to learn how to deal with timestamps in MySQL and I think I accomplished my goals, but I want to double-check two things:

1) Are these doing what I expect

2) Are there faster replacements for these queries / functions

Today

SELECT COUNT(*) AS count FROM log WHERE DATE(datet) = DATE(NOW())

This Week

SELECT COUNT(*) AS count FROM log WHERE YEARWEEK(NOW()) = YEARWEEK(datet)

Last Week

SELECT COUNT(*) AS count FROM log WHERE YEARWEEK(datet) = YEARWEEK(DATE_SUB(NOW(), INTERVAL 1 WEEK))

This Month

SELECT COUNT(*) AS count FROM log WHERE datet > DATE_SUB(NOW(), INTERVAL 1 MONTH)

Last Month

SELECT COUNT(*) AS count FROM log WHERE datet > DATE_SUB(NOW(), INTERVAL 2 MONTH) AND datet < DATE_SUB(NOW(), INTERVAL 1 MONTH)
share|improve this question

migrated from stackoverflow.com Aug 2 '12 at 7:48

This question came from our site for professional and enthusiast programmers.

2 Answers 2

The queries where you have your datet column wrapped within a function (e.g. DATE(), YEARWEEK()) are not efficient due to the fact that MySQL has to evaluate the output of the function for each row in your table. This renders the query as non-sargable (i.e. not able to utilize indexes on the datet column).

It appears you want to select the count of all records within a particular timeframe. To do this, you have to use >=, not =. Also, there is a difference between "this month" and "within one month". I'm assuming you want the former (i.e. if say today was August 3rd, you'd only want records within August, and not from July 3rd, and same thing for week, etc...).

Assuming datet is a DATETIME/TIMESTAMP column and the values of the column will never be "in the future", you can rewrite your queries like so (each of which can utilize an index on the datet column):

-- Today
SELECT COUNT(*) AS count 
FROM   log 
WHERE  datet >= CURDATE()

-- This Week
SELECT COUNT(*) AS count 
FROM log 
WHERE datet >= CURDATE() - INTERVAL CASE WEEKDAY(CURDATE()) WHEN 6 THEN -1 ELSE WEEKDAY(CURDATE()) END + 1 DAY

-- Last Week
SELECT COUNT(*) AS count 
FROM log 
WHERE datet >= (CURDATE() - INTERVAL CASE WEEKDAY(CURDATE()) WHEN 6 THEN -1 ELSE WEEKDAY(CURDATE()) END + 1 DAY) - INTERVAL 1 WEEK
      AND
      datet < CURDATE() - INTERVAL CASE WEEKDAY(CURDATE()) WHEN 6 THEN -1 ELSE WEEKDAY(CURDATE()) END + 1 DAY

-- This Month
SELECT COUNT(*) AS count 
FROM   log 
WHERE  datet >= CURDATE() - INTERVAL DAYOFMONTH(CURDATE()) - 1 DAY

 -- Last Month
SELECT COUNT(*) AS count 
FROM log 
WHERE datet >= (CURDATE() - INTERVAL DAYOFMONTH(CURDATE()) - 1 DAY) - INTERVAL 1 MONTH
      AND 
      datet < CURDATE() - INTERVAL DAYOFMONTH(CURDATE()) - 1 DAY

-- This Year
SELECT COUNT(*) AS count 
FROM   log 
WHERE  datet >= MAKEDATE(YEAR(CURDATE()), 1)
share|improve this answer
    
Wow.... "non-sargable" I'm in way over my head, lol. Thanks, that is exactly what I wanted to know. –  Cole Tarbet Aug 2 '12 at 22:43
    
I'm having a hard time understanding this: CURDATE() - INTERVAL DAYOFMONTH(CURDATE()) - 1 DAY –  Cole Tarbet Aug 2 '12 at 22:48
    
@ColeTarbet Basically you can get the date of the first day of the month of any date by doing (current date - [current day of month - 1] days). So if today was August 23rd, 23 - (23-1) days = aug. 1. August 1st: 1 - (1-1) days = aug. 1. –  Zane Bien Aug 2 '12 at 23:07

I don't know if it's faster, but here's some alternatives :

This Month

SELECT COUNT(*) AS count FROM log WHERE MONTH(datet) = MONTH(NOW())

Last Month

SELECT COUNT(*) AS count FROM log WHERE MONTH(datet) = (MONTH(NOW()) - 1)
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.