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.
Trackback URL: http://asisaid.com/journal/trackback/947.html
Welcome to the world of joins, both inner and outer. Now your blog will really fly. I see you ran into the same thing I did. My host at the moment has 4.01 so things like this won’t work:
SELECT year, ytd
FROM workout
WHERE ytd = (select Max(ytd) from workout where sport = Run);
On my dev server I can do it no problem but when uploading I found that I needed to break it up which, to put it bluntly, sucks. Once they upgrade I will just uncomment the above query and delete the two step-er.
While at my last job, and while doing ColdFusion, we came up with some crazy inner join subqueries that took three steps and turned it into one. The best ones are when you’re query hits more then one DB on more then one machine. You pull your hair out while developing but aaahhhhh when it works it’s a thing of beauty.
Although I honestly believe SQL was created by some evil manager somewhere so workers from that point on could never tell their boss “I can’t get you that info, in that form, the way you want.”
You might want to look at “Toad” … kind of like Enterprise Manager. FYI
I am getting better, I understood some of that.
Mark: Sorry to hear that. I see what you mean about SQL.
It sure is a lot more pleasant than picking through text files trying to work some kind of voodoo though.
Y'know, I know this nice guy that does web hosting that has the new version of MySQL -- I think his portrait is on the side of my page.
Tony: Thanks, I'll check it out.
Christopher: Keep reading this stuff and you'll either go insane or be able to take over the world. Bwahahahaha!
Subqueries are something I have yet to learn, for the most part. Right now the only form of subquery I know is using them with the UNION keyword, which probably doesn’t really count as it merrely appends the rows from the second query onto the result set of the first.
On the other hand, for joins and such, I’ve been learning a lot lately. Being able to use joins is so much better sometimes than linking keys with WHERE clauses, simply because you have the opportunity to do LEFT OUTER JOINs and such, where if there exists a record in the first table that has no match in the second, it’ll replace the fields of the second with NULL values - with a plain WHERE clause it would miss it out entirely.
Yay for geekery.
As you may recall, I recently discovered subqueries . While everything should seem functionally the same, you’ll now be receiving subquery produced information when you view category pages. I think everything seems to be working OK thus far, although …
Please enter your comment entry below. Press 'Preview' to see how it will look.