php developer blog: Don't use NOW() in MySQL SELECT Queries

Don't use NOW() in MySQL SELECT Queries

by Dominik Jungowski

Permanent Link: Don't use NOW() in MySQL SELECT Queries 18. März 2009 comments feed

You maybe want to show all the products that have been added to your database in the last 7 days on your high-traffic website. You are using MySQL and your query probably looks like this:

SELECT *
FROM   product
WHERE  created >= NOW() - INTERVAL 7 DAY

Although being correct, this query is a bad query, because it is using NOW(). NOW() returns the current date and time to the split second. In other words: This query will never land in the cache query!

The better way would be to read the current day in PHP and pass it on to the query:

$date = date('Y-m-d');
$sql = '
SELECT *
FROM   product
WHERE  created >= "%s" - INTERVAL 7 DAY
';
$sql = sprintf($sql, $date);

Now we have a SQL query that perfectly lands in the cache query.

16 comments

david
18.03.2009, 22:03 o'clock

Why not use the CURDATE mysql function?

Matt
18.03.2009, 22:09 o'clock

I know nothing about PHP but doesn't it have the equivalent of parameterized SQL instead of having to resort to using string concatenation and sprintf? We've been trying to keep people from writing that kind of code for obvious reasons for many years now. Although your specific example does not suffer from SQL injection it is only a small step from being extremely vulnerable in other contexts.

Not knowing PHP I'm not sure if this is "standard" and only way to do this or if you've just taken a very poor shortcut.

JoshJordan
19.03.2009, 07:55 o'clock

I don't see any concatenation in the article, but you may be missing the point: not to write a perfect PHP snippet, but to write a query that lands in the SQL cache, which it does :)

bob
19.03.2009, 08:11 o'clock

I thought you were full of crap but then I though about batch transactions.

Its common in an application to prefer the database time vs application time. This statement reminds me that every batch record could have a different timestamp and thats not necessarily desired in batch.

Plus what happens to the efficiency of batch processing if this function is truly invoked per inserted row.

Better would be (if you prefer db time, to select the now() from dual or whatever and then directly populate that in your batch inserts.

I dont do stored procs sorry,.

bob
19.03.2009, 08:21 o'clock

ok thats true but only barely propped up by the fact that your interval was 7 days from today. So this query would be cached for a day IF it was used alot.

What if my date calculation is to the minute?

Then your argument goes out the window. And since most high volume transactions dont occur in granules of 7 days with no time component, I seriously doubt the cacheability of this statement in particular is meaningful.

19.03.2009, 08:40 o'clock

neshaug
19.03.2009, 10:17 o'clock

I don't know MySQL, but in Sybase you could use TODAY() for heavy pages and if you don't need cache any longer than a day.

BTM
19.03.2009, 11:37 o'clock

While it's true that that kind of query would not land in cache, the title of your article is missleading. You suggest not to use NOW() in Queries - I'd say not to use NOW() in SELECT queries - using it in INSERT/UPDATE is still right ;-)

@bob: you could allways expend the date to $date = date('Y-m-d H:i:00'); to cache the query for one minute. You can go as far as changing the $date to use a 5 or so minute intervals.

Giorgio Sironi
19.03.2009, 12:40 o'clock

@matt:
Oh well,
$date = date('Y-m-d');
$query = Doctrine_Query::create('Product p')->where('created >= ? - INTERVAL 7 DAY', $date);
:)

George Entenman
19.03.2009, 13:00 o'clock

> NOW() returns the current date and time to the split second.

What's the problem with that? It just has to ask the OS, which keeps its time in seconds already, right?

– ge

Daniel
19.03.2009, 15:41 o'clock

@George "What's the problem with that?"

He told you, "This query will never land in the cache query!"

Not entirely true, it will, it just will never find a hit again and get pushed out on LRU.

Think about it, two queries operating on a time with microsecond precision, what are the odds that two consecutive queries will have the exact same time when NOW() is called? Not bloody likely.

One way around this is to change your logic slightly, select a fixed range in the query and narrow it down in code, the fixed range will cache and any subsequent queries will hit the cache instead of looking up again.

Redware
20.03.2009, 16:51 o'clock

Why not use a view instead?

Dominik Jungowski
21.03.2009, 14:40 o'clock

Because views are slow. And on a high-traffic website performance is a must

Adrian
29.03.2009, 22:55 o'clock

It is because NOW() a non-deterministic function, meaning the result is always different. MySQL doesn't cache any query with a NDF.

http://www.google.com/search?q=mysql+non-deterministic+functions+cache

Ofer
28.04.2009, 11:08 o'clock

Well, i use NOW() on my application (idlogger) and it works great, why do i need to cache queries on a dynamic application.
Maybe if you need the static query to cahce, there is a way to cache some of the mysql work…

25.07.2009, 17:45 o'clock

SELECT *
FROM product
WHERE created >= CURRENT_DATE() - INTERVAL 7 DAY

php developer blog

Don't use NOW() in MySQL SELECT Queries