[eluser]jedd[/eluser]
[quote author="bfillmer" date="1253221649"]Did anyone else have any ideas on how I can streamline this query some more?[/quote]
That's it? I was waiting on the rest of it.
You didn't say how you went with your EXPLAIN usage. Perhaps this means you didn't try to investigate your problem by yourself?
Assuming you are willing, what happens if you break this down into a number of simpler queries? Do you have a usefully large set of test data to compare to here?
As a general rule, I tend to do counts as single queries, so I can reuse them in other functions. Counts shouldn't be expensive, especially on indexed columns.
Using plain SQL, did you see an improvement if you renamed the duplicated joins using AS (I think I hinted at trying this earlier).