Mysql – Are these MySQL queries efficient

MySQLperformance

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)

Best Answer

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)
Related Question