MySQL FULLTEXT Indexing and Searching

MySQL has supported FULLTEXT indexes since version 3.23.23. VARCHAR and TEXT Columns that have been indexed with FULLTEXT can be used with special SQL statements that perform the full text search in MySQL.

To get started you need to define the FULLTEXT index on some columns. Like other indexes, FULLTEXT indexes can contain multiple columns. Here's how you might add a FULLTEXT index to some table columns:

ALTER TABLE news ADD FULLTEXT(headline, story);

Once you have a FULLTEXT index, you can search it using MATCH and AGAINST statements. For example:

SELECT headline, story FROM news
WHERE MATCH (headline,story) AGAINST ('Hurricane');

The result of this query is automatically sorted by relevancy.

MATCH

The MATCH function is used to specify the column names that identify your FULLTEXT collection. The column list inside the MATCH function must exactly match that of the FULLTEXT index definition, unless your search in boolean mode (see below).

AGAINST

The AGAINST function is where your full text search query goes. Besides the default natural language search mode, you can perform boolean mode searches, and use query expansion.

Boolean Mode Searches

SELECT headline, story FROM news
WHERE MATCH (headline,story)
AGAINST ('+Hurricane -Katrina' IN BOOLEAN MODE);

The above statement would match news stories about hurricanes but not those that mention hurricane katrina.

See the MySQL documentation on Boolean Mode searches for more info.

Query Expansion

The Blind Query Expansion (or automatic relevance feedback) feature can be used to expand the results of the search. This often includes much more noise, and makes for a very fuzzy search.

In most cases you would use this operation if the users query returned just a few results, you try it again WITH QUERY EXPANSION and it will add words that are commonly found with the words in the query.

SELECT headline, story FROM news
WHERE MATCH (headline,story)
AGAINST ('Katrina' WITH QUERY EXPANSION);

The above query might return all news stories about hurricanes, not just ones containing Katrina.

A couple points about Full-Text searching in MySQL:

• Searches are not case sensitive
• Short words are ignored, the default minimum length is 4 characters. You can change the min and max word length with the variables ft_min_word_len and ft_max_word_len
• Words called stopwords are ignored, you can specify your own stopwords, but default words include the, have, some - see default stopwords list.
• You can disable stopwords by setting the variable ft_stopword_file to an empty string.
• Full Text searching is only supported by the MyISAM storage engine.
• If a word is present in more than 50% of the rows it will have a weight of zero. This has advantages on large datasets, but can make testing difficult on small ones.

Do you have any other good tips for fulltext searching and indexing in MySQL?

Full-Text Search using MySQL: Full-Text Search Capabilities

Have you ever thought how does the search functionality is implemented in all the websites! Most of the internet blogs and websites are powered by MySQL database. MySQL provides a wonderful way (Full-text Search) of implementing a little search engine in your website. All you have to do is to have MySQL 4.x and above. MySQL provides full text search capabilities that we can use to implement search functionality.

First let us setup a sample table for our example. We will create a table called Article.

CREATE TABLE articles (
    id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
    title VARCHAR(200),
    body TEXT,
    FULLTEXT (title,body)
);

Also add some sample data in this table. Execute following insert query.

INSERT INTO articles (title,body) VALUES
    ('MySQL Tutorial','DBMS stands for DataBase ...'),
    ('How To Use MySQL Well','After you went through a ...'),
    ('Optimizing MySQL','In this tutorial we will show ...'),
    ('1001 MySQL Tricks','1. Never run mysqld as root. 2. ...'),
    ('MySQL vs. YourSQL','In the following database comparison ...'),
    ('MySQL Security','When configured properly, MySQL ...');

Once the sample data is ready in our table, we can start with our full-text search functionality.

Natural Language Full-Text Searches

Try to execute following Select query on our sample table.

SELECT * FROM articles
    WHERE MATCH (title,body) AGAINST ('database');

You will be able to see following result:

  5        MySQL vs. YourSQL        In the following database comparison ...    
  1        MySQL Tutorial               DBMS stands for DataBase ...                   

In above sql query we used MATCH (title,body) AGAINST (‘database’) to select all the records by performing a full text search on columns title and body.

You can modify this query and create your own version to perform full-text search in your own database.

Boolean Full-Text Searches

It may happen that you want to specify certain keywords in your search criteria. Also you may want to ignore certain keywords. Boolean Full-Text Search can used to perform a full text search for such requirements.

Check the following Select query.

SELECT * FROM articles WHERE MATCH (title,body)
     AGAINST ('+MySQL -YourSQL' IN BOOLEAN MODE);

If you notice the above Select query, we have added IN BOOLEAN MODE in against(). This query will fetch all the records which has MySQL keyword but not YourSQL keyword. Notice the + and – that we have specified before the keywords!

In implementing this feature, MySQL uses what is sometimes referred to as implied Boolean logic, in which:
+ stands for AND
– stands for NOT
[no operator] implies OR

Following are few examples for the boolean search criteria.

‘apple banana’
Find rows that contain at least one of the two words.

‘+apple +juice’
Find rows that contain both words.

‘+apple macintosh’
Find rows that contain the word “apple”, but rank rows higher if they also contain “macintosh”.

‘+apple -macintosh’
Find rows that contain the word “apple” but not “macintosh”.

‘+apple ~macintosh’
Find rows that contain the word “apple”, but if the row also contains the word “macintosh”, rate it lower than if row does not. This is “softer” than a search for ‘+apple -macintosh’, for which the presence of “macintosh” causes the row not to be returned at all.

‘+apple +(>turnover <strudel)’
Find rows that contain the words “apple” and “turnover”, or “apple” and “strudel” (in any order), but rank “apple turnover” higher than “apple strudel”.

Restrictions

The Full-text searches are supported for MyISAM tables only. As of MySQL 4.1, the use of multiple character sets within a single table is supported. However, all columns in a FULLTEXT index must use the same character set and collation. The MATCH() column list must match exactly the column list in some FULLTEXT index definition for the table, unless this MATCH() is IN BOOLEAN MODE. Boolean-mode searches can be done on non-indexed columns, although they are likely to be slow.