Sphinx Search

[Kevlar]

Originally Posted by Wayne Luke

Its more than "improvements", the entire search architecture is being refactored and rewritten to be more scalable.

Is it going to be able to handle 15+ million posts in a timely fashion?

[Simetrical]

I find it hard to believe that anything based on pure MySQL will be able to handle that many posts nearly as well as Sphinx at the moment. MySQL just doesn't have efficient searching right now. You could probably get it to more or less work given enough hardware, if you did stuff like keeping a separate search table and not running updates against it live (to avoid MyISAM's table-wide write locks). But you can't get around the fact that it's just really slow.

[JamesN]

Once Sphinx Search is applied, are there any indexes you can remove or any FullText search settings, etc to switch off/delete?

[amcd]

Yes. From vbulletin admincp, change search to fulltext index so that vb does not try to update the word tables. Then drop the fulltext indices from the post and thread tables.

[JamesN]

Apologies to ask a stupid question but how do I "drop the fulltext indices from the post and thread tables."

I got my server suppot to install Sphinx for me.

[amcd]

You have to run SQL queries to drop indices. If you do not know about this, ask for help from server support. Wrong commands can delete/damage data and it may not be reversible.

[JamesN]

Thanks, this has been done.

Is there anything else that should be done?

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

I'm now getting these errors upon creating new threads. Any idea why?

Database error in vBulletin 3.8.0:

Invalid SQL:

SELECT thread.threadid, MATCH(thread.title) AGAINST ('test') AS score
FROM thread AS thread

WHERE MATCH(thread.title) AGAINST ('test')
AND thread.open <> 10


LIMIT 5;

MySQL Error : Can't find FULLTEXT index matching the column list

[amcd]

Do you have similar threads turned on? If not, then this does not look like a default vbulletin query. Must be some addon or mod.

[JamesN]

Yep, similar threads is turned on. Shouldn't it be for it to work with Sphinx?

[amcd]

Unfortunately, no. The sphinx implementation posted in this thread does not work with similar threads.