Sphinx Search

[eoc_Jason]

You know I was thinking about how to add some of the search features back on the search page.

The current scenario is a basic thread & post index, and then doing delta's of those two. However doing a thread full reindex doesn't take that long (20 seconds or less for me), and the thread table holds very relevant info such as number of replies, number of views, etc..

That's my first goal for this upcoming weekend project. Then I'm going to work on phrases & boolean since sphinx supports that. Google does phrases by using quotes, and so I figure doing a little regular expression matching to set a flag on the search time would work well for sphinx. Also I think I might have to do some character conversion to go between the common boolean options to the sphinx ones.

------------------

To answer someone's question above, usually vB will start to take a dive in search performance after about 2 million posts. It really depends on the amount of content in each post.

The search issue arises like this... A person does a search, so mysql is running that query. In the mean time people can view posts on the forum as usual. However, as soon as a person posts a new thread or reply, that INSERT has to wait until the search is complete. A side effect of that INSERT is that every SELECT afterwards (people just browsing the forum) also has to wait until the INSERT is complete (which is waiting on the search). So queries really start to build up in queue as people are waiting. And if your users are like most that are impatient, if the page doesn't load in a couple seconds they click refresh, which sends ANOTHER query to the DB.... All this has a downward spiral as memory is being used up and the server starts to swap to disk. By then your server is probably completely unresponsive.

----------------

As for the "new posts", that is a simple search based on a 'date' column, it has nothing to do with the post content searching. The relevant fields should already have indexes and it should run pretty good. It can never really be cached by mysql though since the date is different for each person and the results are constantly changing because of replies and such.

---------------

Don't even get me started about the vB search code that can remove valid results due to the extra vB weighting and such.

---------------
Okay, that was a little off track... hehe... Anyhow, I'll try to post whatever new code I come across. I rely on Sphinx to keep my forum search useable, so I won't be going anywhere.

--------------- Added [DATE]1194619418[/DATE] at [TIME]1194619418[/TIME] ---------------

Just realized the thread full re-index thing won't work as I initially hoped. Searching post contents uses only the post index which includes some thread info (like replies). Though I think I can write some secondary cleanup code to re-search and gather realtime info after the sphinx query has run.