See Clear, SQL

One thing I often enjoy doing during the summer is tinkering with my long time coding project SAFARI, the system that powers asisaid and numerous other sites I've designed. Other than pragmatic, urgent changes here and there, I rarely have time to mess with it during the academic year, and while I by no means enjoy programming enough that I would want it to be my “day job,” I do enjoy the change of pace. To that end, over the last three summers, I've been modernizing SAFARI's code base, slowly removing dead code — some of which has been non-functional for the better part of a decade — and adding little features here and there.

Today I undertook cleaning out a lot of dead code from the component that puts together the lists of posts such as you find on the front page of this blog. That particular component probably has some of the messiest code of the entire program and, worse, a few parts still depend on an otherwise retired old theming system I replaced during the summer of 2010. I have been wanting to eliminate that dependency so that I can be completely rid of the inefficient old theming system and stick to the much cleaner, object oriented one I put together two years ago. As part of that, I needed to rewrite the subroutine that lists recently commented upon posts. Previously, that subroutine required several different SQL queries to list those posts, but I wanted to get it down to just one, more efficient query.

To accomplish this, I not only had to unleash my old friend “JOIN,” but also played around with non-JOIN subqueries. The result is not beautiful, but it allows me to get the data I need using just one SQL query:

SELECT uninet_articles.*, UNIX_TIMESTAMP(uninet_articles.gmt), uninet_comments.commentcount, uninet_comments.lastposter, UNIX_TIMESTAMP(uninet_comments.maxgmt) FROM uninet_articles RIGHT JOIN (SELECT aid, (SELECT poster FROM uninet_comments AS a WHERE b.aid = a.aid ORDER BY gmt DESC LIMIT 1) as lastposter, COUNT AS commentcount, MAX AS maxgmt FROM uninet_comments AS b GROUP BY aid ORDER BY maxgmt DESC LIMIT 10) AS uninet_comments ON uninet_articles.aid = uninet_comments.aid LIMIT 10

If any of my SQL inclined friends have suggestions for making the query more efficient, they certainly would be welcomed.