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!
Natural Order Numerical Sorting
I came across an interesting problem today in the CodeIgniter forums. Suppose you have a dataset like this:
1
1.2
1.2.1
1.2.3
1.2.4
2
5
5
5.1
5.7
11
11.1.2
Obviously, the example cited above is sorted, and sorted correctly. Now lets say these values exist in a random order in a database and we want to retrieve them in the same order as above–sorted ascending. The simple SELECT version FROM test ORDER BY version ASC doesn’t quite do the job.
+---------+ | version | +---------+ | 1 | | 1.2 | | 1.2.1 | | 1.2.3 | | 1.2.4 | | 11 | | 11.1.2 | | 2 | | 5 | | 5 | | 5.1 | | 5.7 | +---------+
This is because our data is stored as a string, and the database will sort it as such lexicographically. The thinking human, however, wants it sorted numerically. But we cant, it’s a string. “So convert it to a decimal”, you say. Well, that partly works, except, we loose everything after the second period. For example, SELECT version, (version + 0) FROM test ORDER BY (version + 0) ASC will implicitly cast our field to a decimal. But the results will look something like this:
+---------+---------------+ | version | (version + 0) | +---------+---------------+ | 1 | 1 | | 1.2.1 | 1.2 | | 1.2 | 1.2 | | 1.2.4 | 1.2 | | 1.2.3 | 1.2 | | 2 | 2 | | 5 | 5 | | 5 | 5 | | 5.1 | 5.1 | | 5.7 | 5.7 | | 11 | 11 | | 11.1.2 | 11.1 | +---------+---------------+
The second column is there to show what we’re actually sorting by. As you can see we’re close, but not there. The secret is to sort both numerically AND lexicographically (in that order). The new SQL query will be SELECT version FROM test ORDER BY (version + 0), version ASC. And the results are just what we wanted:
+---------+ | version | +---------+ | 1 | | 1.2 | | 1.2.1 | | 1.2.3 | | 1.2.4 | | 2 | | 5 | | 5 | | 5.1 | | 5.7 | | 11 | | 11.1.2 | +---------+
-
-
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.
