Home / Indexes / Full Text Indexes / Question No: 181

I need to use full-text search feature of MySQL but I am not much expert so far.  Some help about Query Expansion  with regard to full-text search is needed.  I would be wonder, if some explanation about usage of full-text searches with query expansion is provided.

As of version 4.1.1, MySQL provides WITH QUERY EXPANSION modifier with regard to full-text search  (in particular,   its variant  "blind query expansion"). This is generally   useful when a search phrase is too short, which often means that the user is   relying on implied knowledge that the full-text search engine lacks. For   example, a user searching for "database" may really   mean that "MySQL", "Oracle",   "DB2", "Sybase", and "RDBMS" all are   phrases that should match "databases" and should be   returned, too. This is implied knowledge.

Blind query expansion (also known as automatic relevance feedback) is enabled   by adding WITH QUERY EXPANSION following the search   phrase. It works by performing the search twice, where the search phrase for the   second search is the original search phrase concatenated with the few most   highly relevant documents from the first search. Thus, if one of these documents   contains the word "databases" and the word "MySQL", the second search finds the documents that contain   the word "MySQL" even if they do not contain the word   "database". The following example shows this difference:

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

SELECT * FROM articles WHERE MATCH (title,body)
          AGAINST ('database' WITH QUERY EXPANSION);
+----+-------------------+------------------------------------------+
| id | title             | body                                     |
+----+-------------------+------------------------------------------+
|  1 | MySQL Tutorial    | DBMS stands for DataBase ...             |
|  5 | MySQL vs. YourSQL | In the following database comparison ... |
|  3 | Optimizing MySQL  | In this tutorial we will show ...        |
+----+-------------------+------------------------------------------+