Query optimization, select calling function

Question

Consider a large table, which of the below would be faster?

Both queries will select rows where time is greater than the current time.

Calling NOW() within the WHERE clause:

SELECT          *
FROM            myTable
WHERE           time > NOW()

Wrapping the call to NOW() in a sub query:

SELECT          *
FROM            myTable
LEFT JOIN       (
                    SELECT NOW() AS currentTime
                ) AS currentTimeTable ON TRUE                   
WHERE           time > currentTime

Answer 1

"Both queries will select rows where time is no older than 10 days."

Sorry, but your query is incorrect. I tested it in MySQL and got this:

mysql> SELECT DATE(NOW() - 10);
+------------------+
| DATE(NOW() - 10) |
+------------------+
| 2013-11-21       |
+------------------+

21.11.2013 - is the current date and not now() minus 10 days.

You should use DATE_SUB function:

mysql> SELECT DATE_SUB( NOW(), INTERVAL 10 DAY );
+-------------------------------------+
| DATE_SUB( NOW() , INTERVAL 10 DAY ) |
+-------------------------------------+
| 2013-11-11 19:40:38                 |
+-------------------------------------+

Something like this:

SELECT *
FROM   `myTable`
WHERE  `time` > DATE_SUB(NOW(), INTERVAL 10 DAY );

Here is the analysis of both of your query types:

EXPLAIN 
SELECT 
    *
FROM
    users
WHERE
    created > now();

Test data

I tried users table from my Drupal installation.

mysql> EXPLAIN SELECT * FROM users WHERE created < NOW();

+----+-------------+-------+-------+---------------+---------+---------+------+------+-------------+
| id | select_type | table | type  | possible_keys | key     | key_len | ref  | rows | Extra       |
+----+-------------+-------+-------+---------------+---------+---------+------+------+-------------+
|  1 | SIMPLE      | users | range | created       | created | 4       | NULL |    1 | Using where |
+----+-------------+-------+-------+---------------+---------+---------+------+------+-------------+
1 row in set (0.00 sec)



EXPLAIN
SELECT 
    *
FROM
    users
LEFT JOIN (
                    SELECT NOW() AS currentTime
          ) AS tbl ON TRUE
WHERE created < tbl.currentTime;

+----+-------------+------------+--------+---------------+---------+---------+------+------+----------------+
| id | select_type | table      | type   | possible_keys | key     | key_len | ref  | rows | Extra          |
+----+-------------+------------+--------+---------------+---------+---------+------+------+----------------+
|  1 | PRIMARY     | <derived2> | system | NULL          | NULL    | NULL    | NULL |    1 |                |
|  1 | PRIMARY     | users      | range  | created       | created | 4       | NULL |    1 | Using where    |
|  2 | DERIVED     | NULL       | NULL   | NULL          | NULL    | NULL    | NULL | NULL | No tables used |
+----+-------------+------------+--------+---------------+---------+---------+------+------+----------------+
3 rows in set (0.02 sec)

Conclusion

Obviously, the 1-st query is better, as it doesn't require creating any temporary tables. Even with my little sample data, it took 0 seconds to execute the 1-st query and 0,02 secs for the 2-nd.

Answer 2

1) When you talk about date optimisation, using timestamps is the way to go.

2) I suggest instead of calling a function to do the math, create another column for each rows with their timestamps

3) As PeeHaa said on this thread: « When talking about which piece of code is faster (in your OP) you're talking about micro-optimization and is really something you shouldn't have to worry about. ☺

The real question is which piece of code is: better maintainable, readable, understandable. »

I understand what is very large database management but once you've optimised indexes and datatypes you most likely will be good enough.