What are the pros and cons of using full-text search capabilities of MySQL

Question

First of all: I have fruitlessly tried searching Stackoverflow.com for any clues on my problem, however if I have missed anything, please let me know!

---

In my database I have a table containing metadata (i.e. description etcetera) and some other information (file names, etc) about a number of files.

I'd like to provide the users with the ability search for files among these which matches a search query on both filename and description.

What would be the best solution for this problem, should I use the full-text search functions of MySQL or is there a better way of solving this problem? Any performance issues to take into consideration?

Thanks in advance!

Answer 1

MySQLs full text search makes your searches more reliable and flexiable. It's a "natural language search", meaning you can build up rules that the search should adapt to, i.e. order/word boundry/and so on. More over the full text search is adaptable, as an example: Full text query expansion, learns "synonyms" during the search.

Some cons are that it's performance heavy to insert large datasets in a table with full text index and that it only can be used on MyISAM tables.

In your case I would absolutely consider using the full text search, especially for the descriptive column in the table.

Answer 2

Beside the performance and scalability issues, the main drawback is to use full text search in MySQL, you must use MyISAM as the storage engine, which is quite rare in most business nowadays.

Anyway, I would strongly recommend you to try out Sphinx: http://www.sphinxsearch.com/. It is very easy to setup and integrate with MySQL. Prominent users of Sphinx include Craigslist and MySQL.