Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Links Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
Database Tools
SQL Scripts & Samples
Links
» Database Forum
» Sitemap
Free Newsletters:
DatabaseDaily  
News Via RSS Feed


follow us on Twitter
Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

MySQL

Posted Feb 19, 2003

Using Fulltext Indexes in MySQL - Part 2, Boolean searches

By Ian Gilfillan

Part 1 of this article looked at the fulltext index, and how to search on it using an ordinary MATCH() AGAINST(). Even more powerful, (although only available on the newer MySQL version 4), is the ability to do a boolean search. Part 2 of this article examines the possibilities.

You'll use the same table you used in Part 1. The full list of records is:

mysql> SELECT * FROM fulltext_sample;
+---------------------------+
| copy                      |
+---------------------------+
| It appears good from here |
| The here and the past     |
| Why are we here           |
| An all-out alert          |
| All you need is love      |
| A good alert              |
+---------------------------+
To perform a boolean search, the IN BOOLEAN MODE modifier is used. The following query demonstrates the new syntax.

mysql> SELECT * FROM fulltext_sample 
WHERE MATCH (copy) AGAINST ('love' IN BOOLEAN MODE);
+----------------------+
| copy                 |
+----------------------+
| All you need is love |
+----------------------+

An important difference between ordinary MATCH() AGAINST() queries and IN BOOLEAN MODE searches is that the latter does not apply the 50% limit (so the word can appear in more than half the rows).

There are a number of special operators you'll need to know in order to make the most of the boolean search. If no symbol is specified, the appearance of this word causes the relevance of the row to be higher, similar to an ordinary MATCH() AGAINST().

Boolean Search Operators

+The word is mandatory in all rows returned.
-The word cannot appear in any row returned.
<The word that follows has a lower relevance than other words, although rows containing it will still match
>The word that follows has a higher relevance than other words.
()Used to group words into subexpressions.
~The word following contributes negatively to the relevance of the row (which is different to the '-' operator, which specifically excludes the word, or the '<' operator, which still causes the word to contribute positively to the relevance of the row.
*The wildcard, indicating zero or more characters. It can only appear at the end of a word.
"Anything enclosed in the double quotes is taken as a whole (so you can match phrases, for example).

Examples

Let's see some of this action. The first example returns all rows containing the word 'here', but not the word 'past'.
mysql> SELECT copy FROM fulltext_sample WHERE MATCH(copy) 
AGAINST ('+here -past' IN BOOLEAN MODE);
+---------------------------+
| copy                      |
+---------------------------+
| It appears good from here |
| Why are we here           |
+---------------------------+
Compare the difference between the next two examples:
mysql> SELECT copy FROM fulltext_sample WHERE MATCH(copy) 
AGAINST ('here past' IN BOOLEAN MODE);
+---------------------------+
| copy                      |
+---------------------------+
| It appears good from here |
| The here and the past     |
| Why are we here           |
+---------------------------+

mysql> SELECT copy FROM fulltext_sample WHERE MATCH(copy) 
AGAINST ('here +past'IN BOOLEAN MODE);
+-----------------------+
| copy                  |
+-----------------------+
| The here and the past |
+-----------------------+

The first works in a similar way to an ordinary MATCH() AGAINST(), albeit without the 50% threshold. The word 'past' is mandatory in the second search (by default a word is optional), so the other two records are not returned.

The next example demonstrates a common mistake.

mysql> SELECT copy FROM fulltext_sample 
WHERE MATCH(copy) AGAINST ('+are here' IN BOOLEAN MODE);
+---------------------------+
| copy                      |
+---------------------------+
| It appears good from here |
| The here and the past     |
| Why are we here           |
+---------------------------+

The results may appear surprising compared the previous example, but since 'are' contains three or less letters, it is excluded for purposes of the search and is not mandatory.

The next two examples demonstrate a powerful enhancement for searching purposes:

mysql> SELECT copy FROM fulltext_sample 
WHERE MATCH(copy) AGAINST ('aler' IN BOOLEAN MODE);
Empty set (0.01 sec)

mysql> SELECT copy FROM fulltext_sample 
WHERE MATCH(copy) AGAINST ('aler*' IN BOOLEAN MODE);
+------------------+
| copy             |
+------------------+
| An all-out alert |
| A good alert     |
+------------------+


MySQL Archives

Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 




Latest Forum Threads
MySQL Forum
Topic By Replies Updated
MySQL in high availability and transction secure banking application klamor 1 August 28th, 10:24 AM
MySQL rollback UAL225 1 August 28th, 10:15 AM
Browsing a DB file that uses MySql finleytech 1 July 26th, 10:51 AM
php cookie won't pass variable to next page ITdevGirl 0 June 22nd, 12:13 PM


















Thanks for your registration, follow us on our social networks to keep up-to-date