Recent Articles |
Installing A MySQL Database Using A MySQL...
Whenever you purchase or acquire a MySQL database (such as from a website like WebContents.org), chances are it will come as a MySQL injection script. An injection script is simply a SQL script that, when executed...
MySQL IPO, Oracle Unbreakable Support For MySQL?
Matt Aslett writes that Oracle may be considering Unbreakable Support for MySQL and that MySQL Co. is likely to IPO later this year. When MySQL files for...
Convert A MySQL Injection Script For Use In Microsoft...
MySQL Server is the most widely used database management system in the world, primarily because it is open source and free. Hence, most databases you...
Developing Countries Breeding Grounds...
Red Herring's Falguni Bhuta predicts "More (Open Source) momentum in developing countries": "Open-source software's transparent nature and low-cost will make it more popular among developing countries such...
SQL Server Triggers
Triggers are stored procedures which are fired when data is modified in an underlying table. They can evaluate data being added to a table for validation purposes..
SQL Server 2000 Data Types
SQL Server requires that each variable and column in a table should be defined with respect to the type of data it will store. From a bit to a huge image and binary storage types, the allocation is supposed to help the...
Using DataTables More For Improving Performance
There are some features in the System.Data.DataTable class that a lot of developers don't utilize. I base that statement on different code samples I've seen...
Open Source SQL Full Text Search Engine - Sphinx
CLR or TSQL? That is the question. More are more developers are struggling to find the right answer, but the answer really depends on the developer's...
CLR Vs T-SQL And Stored Procedures In SQL Server
CLR or TSQL? That is the question. More are more developers are struggling to find the right answer, but the answer really depends n the developer's individual needs. For classic SQL tasks, the good old TSQL...
MySQL Gets Telecom-Specific
Due to demand, MySQL AB announced the expansion of its telecom-specific services and consulting for MySQL software. Specifically, the company is promoting its MySQL Cluster, a high-end version of the standard...
|
|
|
03.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.
Continue reading this article.
*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.
|
