1

I have worked with Sql Server in past and used it's very nice feature called Sql Full Text Search. Now i have to work with MySql. Can anybody tell me what is equivalent of Full Text Search in MySql? I am using free edition of MySql and not a commercial one. If not, then what else can we do to mimic Full Text Search and get over the limitations of LIKE operator?

Thanks in advance :)

TCM
  • 16,780
  • 43
  • 156
  • 254

3 Answers3

3

See the documentation on the full-text search support in mysql:

http://dev.mysql.com/doc/refman/5.1/en/fulltext-search.html

You will want to apply full text indexes to columns in order to support full text search on those columns. Note that mysql only supports full-text indexing on MyISAM tables.

As an example, and just in case the above link goes dead at some point, see a full example, from the documentation:

mysql> CREATE TABLE articles (
    ->   id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
    ->   title VARCHAR(200),
    ->   body TEXT,
    ->   FULLTEXT (title,body)
    -> );
Query OK, 0 rows affected (0.00 sec)

mysql> 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 ...');
Query OK, 6 rows affected (0.00 sec)
Records: 6  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM articles
    -> WHERE MATCH (title,body)
    -> AGAINST ('database' IN NATURAL LANGUAGE MODE);
+----+-------------------+------------------------------------------+
| id | title             | body                                     |
+----+-------------------+------------------------------------------+
|  5 | MySQL vs. YourSQL | In the following database comparison ... |
|  1 | MySQL Tutorial    | DBMS stands for DataBase ...             |
+----+-------------------+------------------------------------------+

Ref http://dev.mysql.com/doc/refman/5.1/en/fulltext-natural-language.html

Alistair Evans
  • 36,057
  • 7
  • 42
  • 54
  • Hi Kazar, your answer is ver information. But my all tables are InnoDB because i am using Referential Integrity constraints. What purpose would serve by keeping MyISAM engine? Won't most of the real world application database have InnoDB engine? Why is there such restriction? And now should i remove all referential integrity constaints from all my tables to make it work with full text search? – TCM May 18 '10 at 12:39
  • @Nitesh: `InnoDB` is a transactional engine with lock-based concurrency. An index support for such an engine would be quite a hard task, since it would need to be integrated into transaction management, and `InnoDB` has no internal support for non-btree indexes. – Quassnoi May 18 '10 at 12:46
  • Ok, I saw one link on stackoverflow itself :-http://stackoverflow.com/questions/2638698/mysql-full-text-search-workaround-for-innodb-tables which asks to create a temporary tables and delete them which would be very expensive to create and delete on each search operation. Instead i will replicate my few tables on which i want full text search to be done. By the way, i never selected any engine type in Sql Server 2005 and it still supported referential integrity and even Sql full text search. In fact, full text search is supported since sql server 2000 and it got better in 2005 – TCM May 18 '10 at 12:54
2

On MyISAM tables, MySQL supports FULLTEXT indexes:

CREATE TABLE mytable (id INT, caption TEXT, content TEXT, FULLTEXT KEY fx_mytable_caption_content (caption, content)) ENGINE=MyISAM;

SELECT  *
FROM    mytable
WHERE   MATCH(caption, content) AGAINST ('my search query')
Quassnoi
  • 413,100
  • 91
  • 616
  • 614
1

You can create a Full-Text index over one or more columns. You can then use Full-Text search queries to query the table. See MySQL Full-Text index and MySQL Full-text Search functions.

A typical query looks like:

 SELECT * FROM books
 WHERE    MATCH (title, summary) AGAINST ('harry potter')
 ORDER BY MATCH (title, summary) AGAINST ('harry potter')
 LIMIT 0, 10
Salman A
  • 262,204
  • 82
  • 430
  • 521
  • Perhaps better to assign the result of the match to a name, and then order by that name? E.g. WHERE MATCH (title, summary) AGAINST ('harry potter') AS relevance ORDER BY relevance? MySql will probably optimise it away, but still... – Alistair Evans May 18 '10 at 09:55