Thursday, 1 December 2016

Full-Text Search using Mysql

Search is very basic functionlity in website.Everyone wants a better search for their website so in this post i will show you how to create a full text search using mysql. All you need is mysql 4.X version or above.MySQL provides full text search capabilities that we can use to implement search functionality.

Full text search in mysql

CREATE TABLE post_articles (
    title VARCHAR(200),
    body TEXT,
    FULLTEXT (title,body)

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

INSERT INTO post_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 ...');

Now natural language full text search like :

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

his query will search against word "database"  in title and body.

Boolean Full-Text Searches : sometime 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.

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

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!

+ stands for AND
– stands for NOT

Following are few examples for the boolean search criteria.

‘cricket football’
Find rows that contain at least one of the two words.

‘+cricket +football’
Find rows that contain both words.

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

‘+cricket -hockey’
Find rows that contain the word “cricket” but not “hockey”.

I hope you like the post please comment and share.

Featured post

How to create dynamic schema in mongodb

Sometime what we need in our application is to create mongodb schema for collection and some of our collection schema are same but names are...

Popular Posts