Bug #24796 colculation based on now() not beeing optimized in where conditions

• Submitted: 4 Dec 2006 12:36
• Modified: 5 Dec 2006 13:21
• Reporter: Andreas Schultz
• Status: Not a Bug
• Category: MySQL Server
• Severity: S5 (Performance)
• Version: 5.1.14
• OS: Linux (Linux)
• CPU Architecture: Any

[4 Dec 2006 12:36] Andreas Schultz

Description:
where expression that use a calculation involving now() are not properly optimized.

not optimized case:
mysql> explain select * from dialups where updated<=NOW()-3600;
+----+-------------+---------+------+---------------+------+---------+------+--------+-------------+
| id | select_type | table   | type | possible_keys | key  | key_len | ref  | rows   | Extra       |
+----+-------------+---------+------+---------------+------+---------+------+--------+-------------+
|  1 | SIMPLE      | dialups | ALL  | updated       | NULL | NULL    | NULL | 456468 | Using where |
+----+-------------+---------+------+---------------+------+---------+------+--------+-------------+

optimized case:
mysql> explain select * from dialups where updated < now();
+----+-------------+---------+-------+---------------+---------+---------+------+------+-------------+
| id | select_type | table   | type  | possible_keys | key     | key_len | ref  | rows | Extra       |
+----+-------------+---------+-------+---------------+---------+---------+------+------+-------------+
|  1 | SIMPLE      | dialups | range | updated       | updated | 4       | NULL |   10 | Using where |
+----+-------------+---------+-------+---------------+---------+---------+------+------+-------------+
1 row in set (0.01 sec)

There should be now difference in index usage in both cases, now() and now() - 3600 are constant across both querys.

Andreas

How to repeat:
compare key usage on expressions like:

explain select * from dialups where updated<=NOW()-3600;

vs:

explain select * from dialups where updated < now();

[5 Dec 2006 13:21] Valeriy Kravchuk

Thank you for a problem report. Sorry, but it is not a bug. Look:

mysql> select now(), now()-3600, cast(now()-3600 as datetime);
+---------------------+-----------------------+------------------------------+
| now()               | now()-3600            | cast(now()-3600 as datetime) |
+---------------------+-----------------------+------------------------------+
| 2006-12-05 12:35:13 | 20061205119913.000000 | NULL                         |
+---------------------+-----------------------+------------------------------+
1 row in set, 1 warning (0.01 sec)

mysql> show warnings\G
*************************** 1. row ***************************
  Level: Warning
   Code: 1292
Message: Incorrect datetime value: '20061205119913.000000'
1 row in set (0.00 sec)

So:

mysql> explain select * from dialups where updated = now();
+----+-------------+---------+------+---------------+------+---------+-------+--
----+-------------+
| id | select_type | table   | type | possible_keys | key  | key_len | ref   | r
ows | Extra       |
+----+-------------+---------+------+---------------+------+---------+-------+--
----+-------------+
|  1 | SIMPLE      | dialups | ref  | k1            | k1   | 9       | const |
  1 | Using where |
+----+-------------+---------+------+---------------+------+---------+-------+--
----+-------------+
1 row in set (0.00 sec)

mysql> explain select * from dialups where updated = now() - 1;
+----+-------------+---------+------+---------------+------+---------+------+---
----+-------------+
| id | select_type | table   | type | possible_keys | key  | key_len | ref  | ro
ws  | Extra       |
+----+-------------+---------+------+---------------+------+---------+------+---
----+-------------+
|  1 | SIMPLE      | dialups | ALL  | k1            | NULL | NULL    | NULL | 32
768 | Using where |
+----+-------------+---------+------+---------------+------+---------+------+---
----+-------------+
1 row in set, 1 warning (0.00 sec)

mysql> explain select * from dialups where updated = date_sub(now(), interval 1
 second);
+----+-------------+---------+------+---------------+------+---------+-------+--
----+-------------+
| id | select_type | table   | type | possible_keys | key  | key_len | ref   | r
ows | Extra       |
+----+-------------+---------+------+---------------+------+---------+-------+--
----+-------------+
|  1 | SIMPLE      | dialups | ref  | k1            | k1   | 9       | const |
  1 | Using where |
+----+-------------+---------+------+---------------+------+---------+-------+--
----+-------------+
1 row in set (0.00 sec)

So, use date_sub() or date_add() in cases like this (see http://dev.mysql.com/doc/refman/5.1/en/date-and-time-functions.html) to get proper results and indexes used.