Home arrow MySQL arrow Page 5 - Getting Started With MySQL's Full-Text Search Capabilities
MYSQL

Getting Started With MySQL's Full-Text Search Capabilities


Need a rock-solid, powerful search solution for your PHP/MySQL driven web site? In this article Mitchell introduces us to MySQL's full-text and Boolean search capabilities.

Author Info:
By: Mitchell Harper
Rating: 5 stars5 stars5 stars5 stars5 stars / 119
August 26, 2002
TABLE OF CONTENTS:
  1. · Getting Started With MySQL's Full-Text Search Capabilities
  2. · What is Full-Text Searching?
  3. · What is Full-Text Searching (contd.)
  4. · Full-Text Rules And The MATCH Command
  5. · Performing A Boolean Search
  6. · Conclusion

print this article
SEARCH DEVARTICLES

Getting Started With MySQL's Full-Text Search Capabilities - Performing A Boolean Search
(Page 5 of 6 )

MySQL version 4.0.1 and above can perform complex full-text Boolean searches. I am using MySQL version 4.0.1, which is still in the Alpha stage. If you want to perform Boolean searches then click here to download MySQL version 4.0.

So what exactly is a Boolean search? Put simply, it is a powerful way to include or extract words and phrases from your search criteria. If you've ever searched for something like "+download +games", then you've used a Boolean search engine.

By combining various operators within your search string, you can filter in/out other words, change a words contribution to the relevance value and more. Here's the list of Boolean operators, as listed at MySQL.com:
  • + A leading plus sign indicates that this word must be present in every row returned.
  • - A leading minus sign indicates that this word must not be present in any row returned.
  • By default (when neither plus nor minus is specified) the word is optional, but the rows that contain it will be rated higher. This mimics the behaviour of MATCH() ... AGAINST() without the IN BOOLEAN MODE modifier.
  • < > These two operators are used to change a word's contribution to the relevance value that is assigned to a row. The < operator decreases the contribution and the > operator increases it.
  • ( ) Parentheses are used to group words into sub expressions.
  • ~ A leading tilde acts as a negation operator, causing the word's contribution to the row relevance to be negative. It's useful for marking noise words. A row that contains such a word will be rated lower than others, but will not be excluded altogether, as it would be with the - operator.
  • * An asterisk is the truncation operator. Unlike the other operators, it should be appended to the word, not prepended.
  • " The phrase, that is enclosed in double quotes ", matches only rows that contain this phrase literally, as it was typed.
A Boolean search is performed in much the same way as a normal full-text search, however it includes the IN BOOLEAN MODE keywords, as shown in the example below:

select * from testTable where match(firstName, lastName, details) against ('+DevArticles -flash' in boolean mode);

In the example query above I'm specifying that every row returned must contain the word "DevArticles" but must NOT contain the word "flash". Still working with our test table, which record do you think will have the highest relevance ranking if we executed this query?:

select firstName, match(firstName, lastName, details) against('+mysql -devArticles >flash' in boolean mode) as relevance from testTable where match(firstName, lastName, details) against('+mysql -devArticles >flash' in boolean mode);

Looking at the bulleted list above, the results of this query will be ranked higher if:
  • It contains the word "mysql"
  • It doesn’t contain the word "devArticles"
  • It contains one or more instances of the word "flash"
Here is the result from the query:

The result of our query

How about this query:

select firstName, match(firstName, lastName, details) against('+devArticles -sitecubed' in boolean mode) as relevance from testTable where match(firstName, lastName, details) against('+devArticles -sitecubed' in boolean mode);

In this query, records that contain the word DevArticles and NOT the word siteCubed will rank. The results are shown below:

The results of our query

All of the other records returned a 0 relevance ranking, and are therefore not shown in the results. The best way to become familiar with Boolean full-text searches is to experiment with the various operators, including where they are positioned in the search string (including the use of brackets) and how often they are used.
blog comments powered by Disqus
MYSQL ARTICLES

- MySQL and BLOBs
- Two Lessons in ASP and MySQL
- Lord Of The Strings Part 2
- Lord Of The Strings Part 1
- Importing Data into MySQL with Navicat
- Building a Sustainable Web Site
- Creating An Online Photo Album with PHP and ...
- Creating An Online Photo Album with PHP and ...
- PhpED 3.2 – More Features Than You Can Poke ...
- Creating An Online Photo Album with PHP and ...
- Creating An Online Photo Album with PHP and ...
- Security and Sessions in PHP
- Setup Your Personal Reminder System Using PHP
- Create a IP-Country Database Using PERL and ...
- Developing a Dynamic Document Search in PHP ...

Watch our Tech Videos 
Dev Articles Forums 
 RSS  Articles
 RSS  Forums
 RSS  All Feeds
Write For Us 
Weekly Newsletter
 
Developer Updates  
Free Website Content 
Contact Us 
Site Map 
Privacy Policy 
Support 

Developer Shed Affiliates

 




© 2003-2017 by Developer Shed. All rights reserved. DS Cluster - Follow our Sitemap
Popular Web Development Topics
All Web Development Tutorials