Looking for help with a bad query

Did you know I have the smartest readers in the world? I'm not just saying that. You guys are truly intelligent. And beautiful too. Did I say that? All of my readers are intelligent and strikingly beautiful as well. I'm not just saying that because I really want some help with a query. Honest. Ok, maybe I am. Let me give some background here. I've noticed for a while now that when I post a comment to my blog, the code takes a good 4-5 seconds to respond. Normal page requests run much quicker. However - when a comment is posted I clear the cache for the "Recent Comments" list you see below. I extracted the SQL for it and ran it within MySQL's Query Browser and confirmed - it was a dog. For the lift of me I can't figure out why. The query simply asks for the last 5 comments. It joins against the entries table to get the entry title as well. Here is the SQL. The value next to date_add is dynamic as is the blog in the where clause. That value is set with a cfqueryparam.
   view plainprintabout
 select e.id as entryID,
 e.title,
 c.id,
 c.entryidfk,
 c.name,
 c.comment,
 date_add(c.posted, interval -1 hour) as posted
 from tblblogcomments c
 inner join tblblogentries e on c.entryidfk = e.id
10  where     blog = 'Default'
11  order by c.posted desc
12  limit 5
The issue seems to be the join. If I just get the last 5 comments it runs instantly. However, from what little SQL skills I have, a join like this shouldn't be so slow. I've got an index on comment.id and comment.entryidfk. Entry has an index on it's ID and the blog column. Here is the result of the EXPLAIN: Unfortunately nothing really here makes sense to me. This seems to only be an issue with a BlogCFC install with a lot of data as I don't see it on my test version. If folks are bored and want to recreate this locally, just download BlogCFC and fill it with some random data. (I'd be willing to give folks an export, but I'd need to prune out the email addresses from my commenters.) If worse comes to worse, I'm going to mod my own copy to get just the comment data and follow it up with a loop to get the entry data.

Quick TailView Tip

As I mentioned a few days ago, I'm a huge fan of using CFLOG for debugging. To go along with that I make heavy use of TailView in ColdFusion Builder. TailView "monitors" a log file. Every time the file is updated it will automatically show the new lines. This is much easier than using the log viewer built into the ColdFusion Administrator. One of the cooler features of TailView, and something that may not be obvious, is that you can apply filters based on search criteria. This can make certain log lines stick out more than others. Here is a quick video demonstration.

Looking For: Writing ColdFusion in Visual Studio

A reader asked me this morning about plugins/extensions/etc for doing ColdFusion development in Microsoft's Visual Studio. He was able to find Flex support (Amethyst and Tofino) and is hoping something may exist for ColdFusion. Anyone have a suggestion?

Making Subclipse recognize a project checked out from Subversion

When I typically work with Subclipse, I begin my project doing a checkout. Today I added Subclipse to install of ColdFusion Builder where one of the projects was already pointing to a working directory. I expected, perhaps foolishly, that Subclipse would recognize the .svn folder and just - um - do it's thing. No joy. I happened to guess upon a working solution though and thought I'd share with others. Warning - I have yet to actually commit or update - but I did confirm that I could compare against the latest in the repository.

1) Right click on your project.

2) Select Team.

3) Select Shared Project.

This is the part I was most unsure of. I didn't want to share - the project was already shared. But Subclipse recognized that the project was already shared and noted that. I clicked the Finish button and then stood back.

Eclipse began to chew. And chew. Progress windows opened up and closed by themselves. After about 30 seconds or so the demonic possession seemed complete - but I still didn't have little funky graphics next to my files signifying their state. On a whim I tried a Refresh and that seemed to do the trick.

Hope this helps others.

ColdFusion Builder Tip - Finding a file

I'm working with a new client that has a rather large and complex code base. Since I'm still kinda new to the project I don't know where everything exists. I needed to find a particular file name within the project and discovered that ColdFusion Builder (Eclipse really!) provides a simple way to do it.

[More]

Sneak preview of CFLib via Bolt

I uploaded this to RIAForge this weekend, but for folks who are not yet on the pre-release, I thought I'd share a few screens from my CFLib Bolt Extension. I've given it the cool name of: CFLibExtension. Yes, my imagination was at an all time low. You can download the bits from the RIAForge project, and if you did have Bolt installed, here is how it would work.

First, right click on a file, and click into the CFLib menu:

[More]

What's that on RIAForge?

First off - sorry for the downtime at RIAForge this weekend. I was out of town until yesterday afternoon, and just got time to work on it today. Turned out to be kind of a silly issue. The host had contacted the account owners (other people at Adobe) about a required IP update, and I never got the news. I've made sure they know to contact me from now on so something like this shouldn't happen again.

Oh... and is it just me - or is there a new category up at RIAForge? Obviously I can't talk much about it, so please don't ask me details here, but I was told to go ahead and set it up, so perhaps it's time to start getting real excited? Pay special attention to this project, especially the screen shots. In case it isn't obvious: Yes, that's browsing and downloading RIAForge projects direct from Bolt.

Multi-file search and replace in Eclipse

Ok, a big thanks goes out to Scott Stroz for this one. I've been using Eclipse for a while now but never needed to do a multi-file search and replace until last week. Turns out it is rather simple, but I had never really noticed the feature before. I figure a quick blog post may help others, and since I know I'll probably forget in six months, it will help me as well.

[More]

Riddle me this, OSX Firefox Users

As the manager of ColdFusionBloggers.org, I view a heck of a lot of RSS feeds. I've noticed that the description field is always (nearly) impossible to read:

Yet with Firefox under Windows, the same feed's description field is much more easy to read:

I don't really read RSS feeds with Firefox, I just view them in order to get information for adding feeds to my aggregator, but I've wondered if anyone else has ever noticed this?

p.s. Maybe I should just shut up and be happy that Firefox 3 isn't slow as molasses like Firefox 2 was under OSX!

Yet another ADC article on Dreamweaver: Creating a Spry XML data set

Just a quick note to point out another of my Adobe Developer Connection articles has been published: Creating a Spry XML data set. The title doesn't make it exactly clear, but this is a Dreamweaver CS4 related article.

More Entries