Full text search in mysql

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.

view plaincopy to clipboardprint?
  1. CREATE TABLE articles (  
  2.     id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,  
  3.     title VARCHAR(200),  
  4.     body TEXT,  
  5.     FULLTEXT (title,body)  
  6. );  

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

view plaincopy to clipboardprint?
  1. INSERT INTO articles (title,body) VALUES  
  2.     (‘MySQL Tutorial’,‘DBMS stands for DataBase …’),  
  3.     (‘How To Use MySQL Well’,‘After you went through a …’),  
  4.     (‘Optimizing MySQL’,‘In this tutorial we will show …’),  
  5.     (’1001 MySQL Tricks’,’1. Never run mysqld as root. 2. …’),  
  6.     (‘MySQL vs. YourSQL’,‘In the following database comparison …’),  
  7.     (‘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.

view plaincopy to clipboardprint?
  1. SELECT * FROM articles  
  2.     WHERE MATCH (title,body) AGAINST (‘database’);  

You will be able to see following result:

MySQL vs. YourSQL In the following database comparison …
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.

view plaincopy to clipboardprint?
  1. SELECT * FROM articles WHERE MATCH (title,body)  
  2.      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!