My New Friend

I haven't done a really geeky post in quite awhile. Prepare yourself — here comes a megadose of 100% pure geekiness.

I've been using SQL databases for about five years now, and I've slowly learned the Structured Query Language that goes with it mostly out of necessity — I need to do something new, so I learn a new bit of syntax. Tonight, I did a bit more of that. You see, I designed SAFARI (the CMS that runs this blog) so that it creates an entry for each posting in the table known as “articles.” Besides that entry, it also creates numerous entries in the “objects” table for things such as what categories an entry belongs to. Essentially, the objects table is a metadata table.

Now, the problem arises when I want to view a category, such as “Computers and Technology.” Previously, I asked MySQL to collect all of the article ID's (or aids) in the objects table that belonged to the Computers and Technology's parent object id (or pid), then I sent another MySQL query which selected all of the items in the articles table that had one of those article ids. This is really inefficient, not only because it requires two queries, but also because of future growth: if there are 1,000 articles in “Computers and Technology,” the second query must include every one of those ids. Why? Because, for instance, if I am sorting by date, it can only find out which of those 1,000 articles must be requested to get the last twenty posts by actually requesting them. Sure, I could presume that the twenty highest numered ID's were the most recent, but I think that's sloppy.

So, tonight I read the MySQL manual and learned about subqueries. With a subquery, MySQL can be told I want all of the items in articles that match one of the appropriate objects in the objects table. To present a simple example, I am basically saying to MySQL, “Give me every article in 'articles' that has an object in the 'objects' table that belongs to the 'Computers and Technology' parent object.” At first, this didn't work, so I read a bit more closely and found out that my old, 4.0.x version of MySQL was incompatible with subqueries. Thirty minutes later, I upgraded the server to MySQL 4.1.x and the subqueries worked!

My code will be more efficient in no time!

What does this bode for the future? I'm not ready to unveil my master plan, but it means some exciting changes for the currently hibernating OfB, as well as asisaid, in the coming days and weeks. I'll talk about this more very soon.