MySQL: The right way to calculate MTD

Question

I keep seeing answers like this: https://stackoverflow.com/a/21277074/4236404 being the default accepted answer for how to get the Month to date rows in a MySQL query.

The current answer(s):

SELECT id, created
  FROM table1
 WHERE MONTH(created) = MONTH(CURDATE())
   AND YEAR(created) = YEAR(CURDATE())

This query will work, but it's inefficient and MySQL will perform a full scan to get the results.

Therefore the correct way to get the rows for a Month to Date (MTD) query is as follows (I will use PDO and PHP to demonstrate:

//get the first day of the month
$month_first_day = date( "Y-m-01" );

//select the record where the created date is equal to or bigger than the first day of the current month.
$stmt = $pdo->prepare('SELECT id, created FROM table1 WHERE created >= :first_day_of_month');
$stmt->bindParam(':first_day_of_month', $month_first_day);
$stmt->execute();
$realm_data = $stmt->fetch();

Bonus queries:

To get all records for today do the following:

$today = date( "Y-m-d" );
$stmt = $pdo->prepare('SELECT id, created FROM table1 WHERE created >= :today');
$stmt->bindParam(':today', $today);
$stmt->execute();
$realm_data = $stmt->fetch();

To get all the records for yesterday do the following:

$today = date( "Y-m-d" );
$yesterday = date( "Y-m-d", time()-86400 );
$stmt = $pdo->prepare('SELECT id, created FROM table1 WHERE created >= :yesterday AND created < :today');
$stmt->bindParam(':today', $today);
$stmt->bindParam(':yesterday', $yesterday);
$stmt->execute();
$realm_data = $stmt->fetch();

To get all the records for the last 7 days do the following:

$seven_days_ago = date('Y-m-d', strtotime('-7 days'));
$stmt = $pdo->prepare('SELECT id, created FROM table1 WHERE created >= :seven_days_ago');
$stmt->bindParam(':seven_days_ago', $seven_days_ago);
$stmt->execute();
$realm_data = $stmt->fetch();

Assumptions/Tip:

The column in MySQL is set to the datetime format.

Answer 1

MySQL does not allow creating expression/functions based indexes there is a workaround which is possible from MySQL 5.7+ with generated columns which can be indexed.
Makes sure to make the tables as InnoDB which can make indexes on non stored generated columns.

CREATE TABLE t (
 d DATETIME 
 , dy INT AS (YEAR(d)) 
 , dm INT AS (MONTH(d))
 , INDEX(dy, dm)                    
) Engine = InnoDB ; 

Explain

EXPLAIN
SELECT 
 *
FROM 
 t
WHERE
    MONTH(d) = MONTH(CURDATE())
  AND
    YEAR(d) = YEAR(CURDATE())

Result

| id  | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra       |
| --- | ----------- | ----- | ---------- | ---- | ------------- | --- | ------- | --- | ---- | -------- | ----------- |
| 1   | SIMPLE      | t     |            | ALL  | dy            |     |         |     | 19   | 78.95    | Using where |

see demo

Yes i know it says ALL but MySQL is right here. the result returned 15 records the table is 19 records.

Also you need to know the rows output in the explain output is a expected number off record for the InnoDB engine it's not the actual number.

But 15 times a random disk I.O (6ms * 15) and stream it is (alot) more expensive then a full table scan which does one random disk I.O (6ms * 1) and can stream the data in one go.

MySQL's optimizer/execution is costs based which calculates relative costs based on random disk I/O which are the most expensive.

But notice it said possible key dy MySQL knows it can use the index now for

WHERE MONTH(d) = MONTH(CURDATE()) AND YEAR(d) = YEAR(CURDATE())

Only MySQL was thinking iám not going to do more work by reading the index.

Still waiting until MySQL supports expression/functional indexes maybe they add in later a MySQL 8 version.
MySQL 8 is still worked on and getting updates.

CREATE INDEX index_name ON table (YEAR(d));

But i think it will not happen that soon, MySQL does support it more or less with the workaround i used so it don't think they are in a hurry to implement it..

MySQL before MySQL 8.0.16 also didn't support CHECK CONSTRAINTS they also didn't had a hurry to implement it because MySQL already supported it more or less with a VIEW with a CHECK OPTION to give a good example.

Edit:

Seams i was speaking to soon that MySQL wouldn't support that feature expression/functional indexes soon.

i noticed MySQL 8.0.13 update added expression/functional indexes.

Functional Key Parts

A “normal” index indexes column values or prefixes of column values. For example, in the following table, the index entry for a given t1 row includes the full col1 value and a prefix of the col2 value consisting of its first 10 characters:

MySQL 8.0.13 and higher supports functional key parts that index expression values rather than column or column prefix values. Use of functional key parts enables indexing of values not stored directly in the table. Examples:

CREATE TABLE t1 (col1 INT, col2 INT, INDEX func_index ((ABS(col1)))); CREATE INDEX idx1 ON t1 ((col1 + col2)); CREATE INDEX idx2 ON t1 ((col1 + col2), (col1 - col2), col1); ALTER TABLE t1 ADD INDEX ((col1 * 40) DESC);

see source

In MySQL 8.0.13+ you can do

CREATE INDEX index_name ON table (YEAR(created), MONTH(created));