Greetings. My first post.
I'm running a fairly popular wordpress blog (about a million page views a month).
I have a plugin called "recent comments" which lists the last 20 or so comments in the sidebar of the site. However, the queries it makes are very disk and CPU intensive. It's Joining two very large tables together (left join). It takes the post title, author and an excerpt of the comment and melds them together.
These joins cannot be cached in memory because they contain TEXT columns. I was wondering if anyone knew if I could create some kind of index to help speed up these queries. They're killing my site really, and I do not want to remove this functionality from the site.
I was considering re-structuring the way "recent comments" and "recently commented" items are displayed (i.e., separately, instead of via a Joined query and temp table.. but I'm at a loss here.
Any suggestions?