Recent
Articles |
Review: Refactoring Databases
Scott Ambler and Pramod Sadalage wrote Refactoring Databases, they say, "to share their experiences and techniques at evolving database schemas via refactoring". The book, particularly in the...
Sql Server Interview Questions on File Server and...
One of major things in two of this approach is of working with data but both of these works in different manner. In File Server Database approach the data is stored in the file and user have to take data directly from the...
Installing Essentials 2007 On A Remote Instance...
Dide you know can use a remote SQL server for the databases? It's true, but there are a few things to be aware of... SQL Reporting 2005 SP1 must be...
CfObjective - Mark Mandel & Transfer ORM
(As before please pardon the writing.) The problem that we need to solve is writing all the objects that model your database. This is a long and painful process.
What Employers Seek In Oracle Candidates
There are hundreds, if not thousands, of high tech computer jobs open for those with Oracle expertise. The highly reputable and popular Oracle products are...
Important Issues Regarding Database Tools
The sql server primary query language is Transact-SQL, an implementation of the ANSI/ISO standard Structured Query Language (SQL) used by both Sql server...
DevWeek: Day 2 Recap
Niels Berglund's "ADO.Net v.Next and the Entity Framework" was the first lecture of the day, I decided to skip Dino Espistos lecture after I read his slides...
Record Locking In SQL Server
Pessimistic and Optimistic Concurrency. In a multi-user environment, there are two models for updating data in a database: optimistic concurrency and pessimistic...
Installing Fusebox
A quick editorial note. I mentioned last week that I would be playing with Fusebox and trying to learn the basics. In case anyone comes in via Google I wanted to be clear that these are my experiences as I learn.
|
|
|
 |
|
06.13.07
MySQL FULLTEXT Indexing & Searching
By
Pete Freitag
MySQL has supported FULLTEXT indexes since version 3.23.23. VARCHAR and TEXT Columns that have been indexed with FULLTEXT can be used with special SQL statements that perform the full text search in MySQL.
To get started you need to define the FULLTEXT index on some columns. Like other indexes, FULLTEXT indexes can contain multiple columns. Here's how you might add a FULLTEXT index to some table columns:
ALTER TABLE news ADD FULLTEXT(headline, story);
Once you have a FULLTEXT index, you can search it using MATCH and AGAINST statements. For example:
SELECT headline, story FROM news
WHERE MATCH (headline,story) AGAINST ('Hurricane');
The result of this query is automatically sorted by relevancy.
MATCH
The MATCH function is used to specify the column names that identify your FULLTEXT collection. The column list inside the MATCH function must exactly match that of the FULLTEXT index definition, unless your search in boolean mode (see below).
AGAINST
The AGAINST function is where your full text search query goes. Besides the default natural language search mode, you can perform boolean mode searches, and use query expansion.
Boolean Mode Searches
SELECT headline, story FROM news
WHERE MATCH (headline,story)
AGAINST ('+Hurricane -Katrina' IN BOOLEAN MODE);
The above statement would match news stories about hurricanes but not those that mention hurricane katrina.
See the MySQL documentation on Boolean Mode searches for more info.
Query Expansion
The Blind Query Expansion (or automatic relevance feedback) feature can be used to expand the results of the search. This often includes much more noise, and makes for a very fuzzy search.
In most cases you would use this operation if the users query returned just a few results, you try it again WITH QUERY EXPANSION and it will add words that are commonly found with the words in the query.
SELECT headline, story FROM news
WHERE MATCH (headline,story)
AGAINST ('Katrina' WITH QUERY EXPANSION);
The above query might return all news stories about hurricanes, not just ones containing Katrina.
A couple points about Full-Text searching in MySQL:
* Searches are not case sensitive
* Short words are ignored, the default minimum length is 4 characters. You can change the min and max word length with the variables ft_min_word_len and ft_max_word_len
* Words called stopwords are ignored, you can specify your own stopwords, but default words include the, have, some - see default stopwords list.
* You can disable stopwords by setting the variable ft_stopword_file to an empty string.
* Full Text searching is only supported by the MyISAM storage engine.
* If a word is present in more than 50% of the rows it will have a weight of zero. This has advantages on large datasets, but can make testing difficult on small ones.
Do you have any other good tips for fulltext searching and indexing in MySQL?
Comments
*Originally published at Pete Freitag's Homepage
About the Author:
Pete Freitag (http://www.petefreitag.com/) is a software engineer, and
web developer located in central new york. Pete specializes in the
HTTP protocol, web services, xml, java, and coldfusion. In 2003 Pete
published the ColdFusion MX Developers Cookbook with SAMs Publishing.
Pete owns a Firm called Foundeo (http://foundeo.com/) that specializes
in Web Consulting, and Products for Web Developers.
|
