Fulltext/vBulletin search pros and cons

[cfish]

I need to implement a 3 character search on my forum and I have the option of either staying with fulltext and configuring MySQL or switching to vBulletin search.

Can someone give me the pros and cons of each in terms of database size and server load etc.

[Floris]

Hello,

Thank you for contacting vBulletin Support.

Fulltext searching uses a search index that is constructed by MySQL itself. This is the default search as of new installations of vBulletin 3.6 since MySQL 4.0 is required.

This can be done within the vBulletin Control Panel under: vBulletin Options -> Search Options.
Larger forums may have a problem with timeouts so should run the queries manually from the MySQL Command Prompt. The queries are:

ALTER TABLE vb_post ADD FULLTEXT INDEX (title,pagetext)
ALTER TABLE vb_thread ADD FULLTEXT INDEX (title)

The minimum and maximum length of words to be indexed is defined by the ft_min_word_len and ft_max_word_len system variables (available as of MySQL 4.0.0). The default minimum value is four characters. The default maximum depends on your version of MySQL. If you change either value, you must rebuild your FULLTEXT indexes. For example, if you want three-character words to be searchable, you can set the ft_min_word_len variable by putting the following lines in an option file: [mysqld]
ft_min_word_len=3
Then restart the server and rebuild your FULLTEXT indexes. Also note particularly the remarks regarding myisamchk in the instructions following this list.

For more on Fulltext Search from MySQL please visit:

http://dev.mysql.com/doc/refman/5.0/en/fulltext-fine-tuning.html

Our documentation about FULLTEXT option in vBulletin can be found here :

http://www.vbulletin.com/docs/html/search_type

The pro is that you can use boolean search and don't have to index each word in a separate table. Mysql.org has more details on the benefits of fulltext search on tables.

I hope this is what you're looking for, if you need more help feel free to ask.


All the best,

[cfish]

OK, so basically, you are saying that fulltext is the best/most efficient option and the vB search is there for those who may not have control over MySQL configuration.

[Floris]

I am not 100% sure, perhaps it depends per configuration / server / situation. But I've moved all my sites to FULLTEXT and the complaints about not finding things from our members has stopped basically.

[Mike Sullivan]

It's not the easiest comparison to make. The MySQL fulltext search is decent, though you'll see a number of the biggest sites moving to a Sphinx solution.

DB size is probably a bit smaller with the FT search. Index (re)builds/updates are faster with the FT search. With the FT search, some of the memory constraints that you run into with vB's search (due to needing to do work in PHP) are pushed to MySQL so searches with multiple keywords can be more efficient.

You will likely see some table locking issues with the FT search if long searches happen. Though you could run into similar things with the vB search itself. The vB search is probably easier to manipulate, simply because there's a frontend to the options and it's in PHP (vs C). As mentioned, you have to change a MySQL setting at startup to get 3 character searches where in vB it's just a matter of flipping an option and reindexing.

If this is for the forum in your sig, the difference in performance would probably be negligible.

[jasonlitka]

Yeah, once you hit a few million posts Sphinx is the only way to go, particularly if you offer the boolean full-text search to your users (which doesn't scale terribly well).

That said, I really don't see any reason to go with the vB search unless you have a large board, Sphinx is not an option, and the FT search is causing table locking.