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

mysql-full-text-searchHave 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.



17 Comments

  • Danish Backer 5 June, 2009, 13:04

    Thanks a lot. I was looking for this.
    But having small problem I am using innoDB tables for making use of commit.
    I think cannot have both these features (FULLTEXT and COMMIT)
    Is any way to do both?
    I mean any alternative?

  • yourfriendship 12 April, 2010, 5:51

    thanks!I was looking for this
    I use have to do is to have MySQL 5.04 but i have a error
    MYSQL no support “FULLTEXT ”
    can you help me!

    • Ashutosh 1 July, 2013, 0:10

      which mysql server version r u using?

      CREATE TABLE `articles` (
        `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
        `title` varchar(200) DEFAULT NULL,
        `body` text,
        PRIMARY KEY (`id`),
        FULLTEXT KEY `title` (`title`,`body`)
      ) ENGINE=MyISAM AUTO_INCREMENT=7 DEFAULT CHARSET=latin1
      
  • Cyril Gupta 23 April, 2010, 12:49

    MySQL fulltext doesn’t work with InnoDB which is an important issue. I think implementing an external solution is a better idea.

  • Jess 25 March, 2011, 19:26

    Thanks much for this. As a newbie with limited experience in programming, I learned some useful stuff. One small frustration
    I added a few very long records to the table, then ran a SELECT* —
    Every record displayed perfectly when I used RUN (cmd), but with Workbench the text just runs off the page, ignoring the newlines. Had to use INTO OUTFILE to see it all.
    Question: Can you suggest a way to force Workbench to keep records on screen?
    …~jess

  • Danyal Ali Butt 4 October, 2011, 20:10

    Great piece of information …….

  • ahmad balavipour 5 December, 2011, 18:08

    Best article about MATCH

  • Alok ranjan dubey 17 December, 2011, 19:11

    Thanks a lot

  • Piotr Sobczyk 11 April, 2012, 13:56

    Thanks for very accessible article about fulltext search. Good job!

  • shashi 18 September, 2012, 13:38

    For enabling Full Text search with InnoDB, we can use sphinx ( http://sphinxsearch.com )

  • plgeda 18 October, 2012, 11:11

    Thanks a lot.

    can please upload the functionality for hibernate full text search ..

  • Fuad 31 March, 2013, 4:49

    Good sharing :)

  • Keshav 10 April, 2013, 12:21

    It is really very good post.
    Thanks for it.

  • Ramandeep Singh 19 June, 2013, 21:12

    very helpful post thank you very much

  • Paul Redmond 23 January, 2014, 23:26

    Great list, thanks!

  • Ram Gurung 18 March, 2014, 11:29

    Thank you bro. This is short but descriptive.

  • Ashish Saxena 1 August, 2014, 13:56

    i need to search “mistake of my life” and when i use (match against) query and search keyword(‘m’) means not full word, then “No Result Found”.

Leave a Reply

Your email address will not be published. Required fields are marked *

Note

To post source code in comment, use [code language] [/code] tag, for example:

  • [code java] Java source code here [/code]
  • [code html] HTML here [/code]

Current ye@r *