Why I don't use sysdate in MySQL

Why I don't use sysdate in MySQL:
When I first started using MySQL, I was happy to see that MySQL had the familiar SYSDATE() function that I'd been using for years in Oracle. I wrote a bunch of code using SYSDATE() in MySQL, and then one day I noticed that my slave database sometimes had higher timestamp values than the master database for the same rows. Upon closer investigation, it turned out that SYSDATE() was the problem. As stated in the MySQL Reference Manual:

In addition, the SET TIMESTAMP statement affects the value returned by NOW() but not by SYSDATE(). This means that timestamp settings in the binary log have no effect on invocations of SYSDATE().

Essentially, this means that if you use SYSDATE() in a DML statement that is replicated, the slave gets the timestamp of when the slave executed the DML, not when the master executed the DML. Given that MySQL timestamps have 1 second granularity these values are often the same (if replication never falls behind), but eventually the slave will fall 1 second or more behind and this will introduce data discrepancies. Once I learned that I went back and updated all of my DML to use NOW() instead of SYSDATE() so my slave would have identical data to the master.

And another thing

Using SYSDATE() in MySQL can also lead to very slow query performance in certain scenarios.

Say I have a post table with ~40 million rows and I want to know how many of them were created in the past 8 hours. I've got an index on the created timestamp, so I think this query will be fast:

```
mysql> select count(*)

-> from post 
-> where created_on >= sysdate() - interval 8 hour;

+----------+
| count(*) |
+----------+
| 2419 |
+----------+
1 row in set (51.41 sec)
```

Despite the index, that query took 50 seconds to run!

Again, the answer is to use NOW() instead of SYSDATE():

```
mysql> select count(*)

-> from post 
-> where created_on >= now() - interval 8 hour;

+----------+
| count(*) |
+----------+
| 2419 |
+----------+
1 row in set (0.00 sec)
```

That's more like it!

But why was the first query so slow? The explain shows how the queries are different:
```
mysql> explain select count(*) from pabeta.app_instance where created_on >= sysdate() - interval 8 hour;
+----+-------------+-------+-------+---------------+-----------------+---------+------+----------+--------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+-------+---------------+-----------------+---------+------+----------+--------------------------+
| 1 | SIMPLE | post | index | NULL | CREATED_ON_INDX | 4 | NULL | 41766790 | Using where; Using index |
+----+-------------+-------+-------+---------------+-----------------+---------+------+----------+--------------------------+
1 row in set (0.00 sec)

mysql> explain select count(*) from pabeta.app_instance where created_on >= now() - interval 8 hour;
+----+-------------+-------+-------+-----------------+-----------------+---------+------+------+--------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+-------+-----------------+-----------------+---------+------+------+--------------------------+
| 1 | SIMPLE | post | range | CREATED_ON_INDX | CREATED_ON_INDX | 4 | NULL | 2418 | Using where; Using index |
+----+-------------+-------+-------+-----------------+-----------------+---------+------+------+--------------------------+
1 row in set (0.00 sec)
```

I think I'll take the range scan over 2000 rows instead of an index scan of the entire 40 Million index entries, thank you very much.

And the MySQL Reference Manual explains why they are different:

The nondeterministic nature of SYSDATE() also means that indexes cannot be used for evaluating expressions that refer to it.

Given these issues I always use NOW() instead of SYSDATE() in all of my MySQL queries.

PlanetMySQL Voting:
Vote UP /
Vote DOWN

DIGITAL JUICE