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.
Before we start, it’s important to know about these keywords:
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).
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
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! 🙂