Databases

CouchDB View Generation

An alternative technology quickly gaining popularity these days is CouchDB, a document-based database system for semi-structured data. I wasn’t sure what that meant at first, so I read as much as I could about it. The result? I couldn’t wait to use it. I decided CouchDB would be a good fit for my next project (which I should be releasing sometime this week BTW) and rolled up my sleeves. Because of the amount of data I’m working with, I hit a few snags along the way with regard to CouchDB view performance. Some of the things I learned, although they […]

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.

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 […]