Why sysdate() Cannot Use Indexes While now() Can in MySQL

Background

During on‑site optimization of a batch of monitoring SQL statements, it was observed that using sysdate() as the range value for a query caused very poor performance because the execution plan could not use an index, while replacing it with now() allowed the index to be used. The following analysis explains this phenomenon.

Quick note: the difference between sysdate() and now() is a long‑standing issue.

Difference Between sysdate() and now()

According to the official MySQL documentation: now() returns a constant time that is the moment the statement begins execution; the same value is returned each time the function is called within that statement. sysdate() returns the exact time at which the function is evaluated, i.e., the precise execution time of the statement.

Example from the documentation demonstrates that two consecutive calls to now() produce identical timestamps, while two calls to sysdate() produce different timestamps.

mysql> SELECT NOW(), SLEEP(2), NOW();
+---------------------+----------+---------------------+
| NOW()               | SLEEP(2) | NOW()               |
+---------------------+----------+---------------------+
| 2023-12-14 15:13:09 | 0        | 2023-12-14 15:13:09 |
+---------------------+----------+---------------------+

mysql> SELECT SYSDATE(), SLEEP(2), SYSDATE();
+---------------------+----------+---------------------+
| SYSDATE()           | SLEEP(2) | SYSDATE()           |
+---------------------+----------+---------------------+
| 2023-12-14 15:13:19 | 0        | 2023-12-14 15:13:21 |
+---------------------+----------+---------------------+

Because now() is constant, the optimizer can evaluate the expression at parse time and use the index; sysdate() is evaluated at execution time, so the optimizer cannot treat it as a constant, leading to a full table scan.

Test Example

A test table t1 is created with an indexed create_time column. Data is inserted, and queries using both functions are examined.

mysql> CREATE TABLE t1(
    id INT PRIMARY KEY AUTO_INCREMENT,
    create_time DATETIME DEFAULT CURRENT_TIMESTAMP,
    uname VARCHAR(20),
    KEY idx_create_time(create_time)
);

mysql> INSERT INTO t1(id) VALUES (NULL),(NULL),(NULL);

mysql> SELECT * FROM t1;
+----+---------------------+------+
| id | create_time         | uname|
+----+---------------------+------+
| 1  | 2023-12-14 15:34:30 | NULL |
| 2  | 2023-12-14 15:34:30 | NULL |
| 3  | 2023-12-14 15:34:30 | NULL |
| 4  | 2023-12-14 15:34:37 | NULL |
| 5  | 2023-12-14 15:34:37 | NULL |
| 6  | 2023-12-14 15:34:37 | NULL |
+----+---------------------+------+

Running EXPLAIN SELECT * FROM t1 WHERE create_time < SYSDATE(); shows possible_keys and key as NULL, confirming that the index is not used.

id: 1
select_type: SIMPLE
table: t1
type: ALL
possible_keys: NULL
key: NULL
...

Running EXPLAIN SELECT * FROM t1 WHERE create_time < NOW(); shows the optimizer choosing idx_create_time with type: range, demonstrating index usage.

id: 1
select_type: SIMPLE
table: t1
type: range
possible_keys: idx_create_time
key: idx_create_time
...

Trace Analysis

Using MySQL's trace feature, the optimizer's internal decision process is examined for both functions.

Trace for sysdate()

"rows_estimation": [
  {"table": "`t1`", "range_analysis": {"table_scan": {"rows": 6, "cost": 2.95}, "potential_range_indexes": [{"index": "PRIMARY", "usable": false}, {"index": "idx_create_time", "usable": true}]}}
... (subsequent steps show that the optimizer cannot treat sysdate() as a constant and falls back to a full scan)

Trace for now()

"rows_estimation": [
  {"table": "`t1`", "range_analysis": {"potential_range_indexes": [{"index": "idx_create_time", "usable": true}]}}
... (later steps show the optimizer converting now() to a constant, selecting a range scan on idx_create_time)

The trace confirms that now() is converted to a constant during the rows_estimation phase, allowing the optimizer to choose the index, while sysdate() remains dynamic and forces a full scan.

Conclusion

now()

is evaluated once at the start of the statement, producing a constant value that the optimizer can use for index selection and other optimizations. sysdate() is evaluated at the exact moment of execution, making its value nondeterministic; therefore the optimizer cannot treat it as a constant, and queries using it cannot benefit from indexes.

Reference: MySQL 8.0 Date and Time Functions documentation.