What is a fulltext index and when should I use it?

As the title states, what is a fulltext index and when should I use it?

In databases indices are usually used to enhance performance when looking for something defined in your where clause. However when it comes to filtering some text, e.g. using something like WHERE TextColumn LIKE '%searchstring%' then searches are slow, because the way regular database indices work are optimized for matches against the 'whole content' of a column and not just a part of it. In specific the LIKE search which includes wildcards can not make use of any kind of index.

As mentioned in the comment below MySQL needs the MATCH () ... AGAINST syntax to search within a fulltext index; BTW this varies depending on the database vendor. In MS SQL you can use CONTAINS so keep this in mind when you plan to support other databases too.

Fulltext indices work better for regular text, because they are optimized for these type of columns. Very simplified: They split the text into words and make an index over the words and not the whole text. This works a lot faster for text searches when looking for specific words.

A full text index is an index you apply in a MySQL database to text fields that you plan to run a full text search on. A full text search uses the match(field) against('text') syntax. If you want to run a full text search you must have a full text index on the columns you'll be running it against.

There are three types of Full Text searches. I'll quote the manual, because I think it says it best:

> * A boolean search interprets the search string using the rules of a > special query language. The string > contains the words to search for. It > can also contain operators that > specify requirements such that a word > must be present or absent in matching > rows, or that it should be weighted > higher or lower than usual. Common > words such as “some” or “then” are > stopwords and do not match if present > in the search string. The IN BOOLEAN > MODE modifier specifies a boolean > search. For more information, see > Section 11.9.2, “Boolean Full-Text > Searches”. >
> * A natural language search interprets the search string as a > phrase in natural human language (a > phrase in free text). There are no > special operators. The stopword list > applies. In addition, words that are > present in 50% or more of the rows are > considered common and do not match. > Full-text searches are natural > language searches if no modifier is > given. >
> * A query expansion search is a modification of a natural language > search. The search string is used to > perform a natural language search. > Then words from the most relevant rows > returned by the search are added to > the search string and the search is > done again. The query returns the rows > from the second search. The WITH QUERY > EXPANSION modifier specifies a query > expansion search. For more > information, see Section 11.9.3, > “Full-Text Searches with Query > Expansion”.

For more information take a gander at the Full Text Search Reference Page.