Devils Advocate Rick.
What good is it to keep the posts if you limit the date range to search by.
If they can't find them then you might as well delete them.
Just my 2 cents.
Or maybe you are working on this in v8. Hey I could have had a v8. LOL sounds like a commercial.

Much of the popularity (read: traffic) of our site is tied to the Piano Forums.
As are thousands of pages indexed in the search engines.

I'd hate to have to decide which posts to delete (not to mention, it would take an enormous chunk of my time to slog through all the posts).

I guess my lack of technical knowledge leaves me confused about why a search query would lock up the rest of the forums.

Isn't one search = to one connection?
Shouldn't it be able to more or less run in the background without bringing down the rest of the forums?

Lots of people come to Piano World to research information about pianos. It's natural for them to gravitate to the search function.

Well you don't have to delete them, Ricks suggestion is just limit the search to say 1 year or so.
My question is if you limit searches to 1 year then what good is the posts older than 1 year or so. You would have to click forever to find them. So might as well delete them or possibly move them to a archive forum. But you would still have the dilemma of finding them.

I hate scrolling to the right.

When it gets so large the fulltext is slowing things down the only thing you can do is really search in chunks. That's what the newer than / older than selections are for on the search page. Having a 1 year date range doesn't limit you to only last year's posts. You can search any daterange, ie 2004-2005. It's not the optimal solution, but as the size of the db grows, fulltext will slow down.

In simple terms it has to lock the table in order to do the query. When the table is locked then other queries on that table will stack up until it's done.

There are a few solutions that some sites will do such as having a slave database that is strictly just for searches. So when a search is done it's done on the slave and won't block other queries.

There are a few things we're going to try and do to help with search speed, but it's always a sticking point especially when it's done on all posts in the database.

Oh okay. I thought the date range setting limited the newer than/ less than search fields in the advanced search.
You do get a message above the fields showing the "Maximum date range is xxx"

Ok, got it, thanks.

Then in theory, would it be possible to rework the search interface to restrict searches to specific blocks?

I'm thinking of a series of Radio buttons:
* Past Month
* Past 6 Months
* 1 Year Ago (a 6 month search of Aug 2008 - Feb. 2009)
* 2 Years Ago (where it will search Aug. 2007 to Aug. 2008)
* 3 Years Ago (where it will only search Aug. 2006 - Aug. 2007)

I know, probably making it more complicated than it needs to be. It's just that there are still posts with some value going back years. I'd hate to cut people off from those.

By the same token, it's annoying a lot of our members/visitors when the forums lock up, and I'm sure it's scaring away some new potential members.

We already have some pretty decent hardware cranking, I keep hoping there is a coding/development solution lurking in here somewhere.

Search Engines, scrolling through dates manually

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.