Joining the Crowd

Well, I enjoyed my brief moment as t3h l33t subquery h4×0r, but in the end, subqueries seem to fall flat on their face. Two queries with subqueries were taking SAFARI 7-20 seconds to run, a totally unacceptable speed, especially since my goal with SAFARI was to build it in such a way as to allow it to be Slashdotted without performance problems. Conversely, using a join statement, I accomplished the same effect while reducing the processing time to less than two seconds (how much less, I cannot yet say, since I'm still working on developing the perfect query, but I'm expecting it to drop below 1 sec before I am done).

The road to the next generation of SAFARI progresses…

Update (10 July 2006 12:14 AM): OK, so I wanted to see if I could count the number of comments in my comments table in addition to comparing the objects (metadata) and articles (normal article stuff) table all in one query for efficiency. I ended up with what might be best termed a hybrid solution: the meat of the problem is taken care of via two LEFT JOINs, the latter one joining the results of a subquery to the main results. It seems reasonably efficient: it takes a mere 0.0274 seconds to process! How 'bout them apples?