MySQL SELECT Entries Before NOW()

I’m in the business of making things faster. Using NOW() in a SQL query is something I’m going to complain about. Here’s a familiar scenario from the online publishing industry where future dating articles is a commonality:

You have a news site. You need to display only articles that have been published, and one of the criteria is that they need to have a publish_date before now. Easy, peasy, lemon squeezy.

SELECT author, title, body FROM articles WHERE publish_date <= NOW();

That works, right? Yeeeeah, it works, but it isn’t optimal. The problem is that MySQL can’t use the query cache on any query that has NOW() in it (or CURRENT_TIME() or any of these other functions for that matter). The solution I like to use is have PHP generate the timestamp. Even better is to have PHP round the timestamp, like so:

// Calculate time to nearest 15 minutes
$roundness = 60 * 15;
$rounded_now = (round(time() / $roundness) * $roundness);
$sql = "SELECT author, title, body FROM articles WHERE publish_date <= $rounded_now";

Of course, depending on how time sensitive your application is, you may need to change the code from rounding to 15 minutes to something like 5 minutes, or 1 minute. Hey, even rounding to 30 seconds would be better than using NOW() because you can use query cache!

February 19, 2008 | Filed Under Databases, Performance, Statiksoft, Work 

Comments

One Response to “MySQL SELECT Entries Before NOW()”

  1. Dean Proctor on June 28th, 2008 8:40 am

    I think this is a fantastic idea.

Leave a Reply