FULL-TEXT search using MYSQL


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.

Why FULLTEXT ??

MySQL allow text searching by using the LIKE and regular expression. But, these methods have some limitations like:

  • Performance: MySQL need to scan whole table to find the exact text based on pattern in the LIKE  or pattern in the regular expressions.
  • Flexible search: It is difficult to have flexible search query e.g., to find product whose description contains car but not classic.
  • Relevance ranking: No way to specify which row in the result set that is more relevant

Features of MySQL full-text search:

  • Native SQL-like interface: you use SQL-like statement to use the full-text search.
  • Searches are not case sensitive
  • Fully dynamic index: MySQL automatically index the text column whenever the data changes. You don’t need to run the index periodically.
  • It does not take much memory to store the index.
  • It is fast to search based on complex search query.

SearchGet Started

Before we start, it’s important to know about these keywords:

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.

Lets start with some sample table and query:

CREATE TABLE articles (
    id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
    title VARCHAR(200),
    body TEXT,
    FULLTEXT (title,body)
);
// Insert some data into table

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

Here two types of search are possible:

  • Natural Language Full-Text Searches
  • Boolean Full-Text Searches

Natural Language Full-Text Searches

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

This query will fetch all the records which has database keyword.

Boolean Full-Text Searches

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

In above query you see that I have added IN BOOLEAN MODE in against(). This query will fetch all the records which has MySQL keyword but not YourSQL keyword. (+ and – that we have specified before the keywords)

+ stands for AND
- stands for NOT
[no operator] implies OR

Remember:

Not all storage engines support full-text searching. In MySQL, only MyISAM and InnoDB storage engines support full-text search. 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.

Enjoy Coding! 🙂

Reference: http://viralpatel.net/blogs/full-text-search-using-mysql-full-text-search-capabilities/

Advertisements

One thought on “FULL-TEXT search using MYSQL

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s