Home arrow MySQL arrow Page 3 - 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 - What is Full-Text Searching (contd.)
(Page 3 of 6 )

This query returns the following result:

The result of our query

Let's analyze our query. First up, we have the SELECT and FROM parts of our query:

select firstName from testTable

There's nothing unusual about this part of the query: it simply tells MySQL to retrieve the firstName field from the table called testTable. Next up, we have the WHERE clause:

where match(firstName, lastName, details) against('guru');

This is where the power of full-text searching starts. In the first part of the query we call the MATCH command, which tells MySQL to match against the values of the firstName, lastName and details fields when it performs a full-text, natural language search.

When the MATCH command is used as part of the SELECT clause it returns a relevance ranking, which is a positive decimal number. The closer to 0 this number is, the less relevant the record is. This relevance value is determined based on the search expression, the number of words in the indexed fields, as well as the total number of records being searched.

Lastly, we have the AGAINST command. AGAINST is simple enough and accepts just one parameter, which is the string that we're searching for. Later in this article we will see how to perform boolean searches using the AGAINST command in combination with the IN BOOLEAN MODE keywords.

So far so good, right? But how does a full-text search differ from using the LIKE command in a query like this:

select firstName from testTable where details like '%guru%';

... they both return the same result don’t they? Well yes and no. Let's now see how we can determine a relevance ranking for each of the records returned from a MySQL full-text search.

Determining A Relevance Ranking
Still working with our test table, take the following query into consideration:

select concat(firstName, ' ', lastName) as name, match(firstName, lastName, details) against('devArticles') as relevance from testTable where match(firstName, lastName, details) against('devArticles');

This query produces the following results:

The result of our query

In this query I've included the MATCH command in the SELECT clause to return a relevance ranking for each record. The query performs a full-text search against the firstName, lastName and details fields for the string "DevArticles":

where match(firstName, lastName, details) against('devArticles');

The query has returned two records, which contained the string "devArticles" in either their firstName, lastName or details fields. Looking back at our records, we can see that the records for Mitchell and Michael contained the string "DevArticles" in their details fields:

'Mitchell is the founder and manager of devArticles and various other sites across the SiteCubed network'

'Michael is the senior devArticles editor and is a capable Linux/Apache administrator'

The relevance field returned from our query was generated with the following expression:

match(firstName, lastName, details) against('devArticles') as relevance

Looking carefully at the query, we can see that this expression has been used twice: once in the SELECT clause and once in the WHERE clause. MySQL picks up on this and only performs one full-text search on the table and not two, meaning that there is no additional overhead produced for a query like this –- a huge bonus and time saver if a similar query was performed on a table with 5 million rows.

When the MATCH command is used in the WHERE clause, MySQL automatically sorts the rows from highest to lowest relevance. In our previous example query we only returned records that actually had a match against the string "devArticles" when a full-text search was conducted. Here's a query that returns the relevance ranking for every record in our table:

select concat(firstName, ' ', lastName) as name, match(firstName, lastName, details) against('devArticles') as relevance from testTable;

We've left out the WHERE clause, so the resultant records are unordered, as we can see below:

The results of our query
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