Cache queries with dates

This post is about a small tip regarding queries that include dates in the clause. If you are not careful enough, you might be taking a performance hit.

Let’s suppose you have a database table containing articles (with the same name) and there is a field named publishing_datetime. You can store articles with a future date in that table, so that an article will be auto published when that time comes. (Sounds familiar? Oh wait, right… WordPress does it!)

And now, let’s suppose that you want to select the latest 10 articles (which are already published, until now).

One way to write the query in PHP would be:

 
$sql = "
   SELECT *
   FROM articles
   WHERE published_datetime <= '".date("Y-m-d H:i:s")."'
";

This would cause on major problem. MySQL has an internal caching mechanism, and it caches queries based on their string. So it will cache this query, only for a single second, since in the next second the query string that php sends to the database changes. This could potentially cause a lot of problems to high traffic websites.

Actually, the same happens if you are using MySQL’s function NOW() (or CURDATE()).

So, what’s the solution? If the seconds are not that important (who cares if the article is published 59 seconds –worst case– sooner?) you should just drop the seconds from the query. That way, it is at least cached for one whole minute, which could make a huge difference.

Leave a Reply

Your email address will not be published. Please enter your name, email and a comment.

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>