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!
The New SPEEDtv.com
February 6th has come, and it has gone. After much toil and many, many long days, the largest development project MindComet has ever undertaken launched on time. That statement alone is worth celebrating.
Whether the project ends up being a glorious success to the end users or not, as a developer I can make it something to be proud of in the lessons I take away from it. For example, here’s one thing I discovered about myself: I like BIG. I like the challenge…the complexity…the impact. Big forces you to approach problems in a different, often better, way. Big challenges your development processes and methodologies when you may otherwise be content. Big requires skills, tools, and architectures you don’t normally get to use with small. Big magnifies your weaknesses, and exposes your flaws–refining those who learn from it. And in the end, if you can make it to the finish; if you can reach your goal–you’re a cut above the rest. Here’s to big. Here’s to the SPEEDtv development team.
-
-
About
Sean Murphy is a software developer and lifestyle entrepreneur currently living near Orlando, FL. He loves working with smart people, writing cutting edge software, sharing knowledge, and most of all, steak.
