im sure the ordering of the joins has some effect too, ill have to readup again on my rusty mysql query optimizations (im mostly a postgres guy
-- there is an index key on the posting date, so if that is restricted first before the rest of the joins occur in the query, then that should speed things up.
Of course Pianoworld's suggestions above of a sliding time range window for the searches is a good idea, but whatever is the biggest search in terms of # of posts included in the search range will be the slowest - so those 1 year ranges will be quite large.
there are 132k posts since jan 1 2009, and 336k posts jan 2008-2009. so we're looking at > 300k/year. so a 1 year search limit would be about 4 times fewer posts. if impact on the query is O(n^2) or larger, that'd speed things up, but i dont know how the query engine will react to that - depends on mysql internals for innodb that i am not familiar with - i spose we can only try.
