birchy - 4:02 am on Jul 16, 2013 (gmt 0)
Thanks so much for your comment, it made me re-evaluate some assumptions. Turns out the biggest problem was not the size of the table used in the main query, but the additional lookups in a related table performed on each record returned. I have a couple of options on that related data (Ajax it, or de-normalize it and pull it into an array with one query at the beginning of the process, reducing a couple of hundred queries down to one). I've been running with it totally disabled for over an hour now and am hoping the noticable improvement in CTR is directly related to the faster load times.
As for EXPLAIN SELECT, I wasn't familiar with it, and the only thing it turned up was one non-indexed columns in the WHERE, for a 1 char VARCHAR flag. Removing that WHERE reference didn't significantly affect load speed. Anyone have any thoughts about the value of indexing such a critter or not?