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:
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:
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:
Next: Full-Text Rules And The MATCH Command >>
More MySQL Articles
More By Mitchell Harper
