MySQL FULLTEXT Indexing and Searching
MySQL has supported
FULLTEXT indexes since version
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
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 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 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.
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
- 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_fileto an empty string.
- Full Text searching is only supported by the
- 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?
- Sphinx - Open Source SQL Full Text Search Engine - November 1, 2006
- Updated SQL Reserved Words Checker - March 28, 2006
- SQL to Select a random row from a database table - September 14, 2005
- Insert Delayed with MySQL - August 2, 2005
- Multiple Inserts with MySQL - June 10, 2005
Warning: mysql_fetch_array() expects parameter 1 to be resource, boolean given in C:\xampp\htdocs\test\search2.php on line 35
my code is
<form name="form" action="search2.php" method="get">
<input type="text" name="q" />
<input type="submit" name="Submit" value="Search" />
$var = @$_GET['q'] ;
$trimmed = trim($var);
$quary = "'%".$trimmed."%'";
$select = "SELECT *,
MATCH(news) AGAINST('$trimmed') AS score
WHERE MATCH(news) AGAINST('$trimmed')
ORDER BY score DESC";
die ("Database not connected");
mysql_select_db ("omassery", $con);
echo $result = mysql_query ($select, $con);
while ($row = mysql_fetch_array ($result))
Ex: if we are search for blue t-shirt, the query displays all the blue t-shirt’s and t-shirt’s keyword
Helped me indeed !!
- Upcoming CFML Conferences in April 2017
- CFSummit 2016 Slides
- Securing Legacy CFML - dev.Objective() 2016 Slides
- My CFSummit 2015 Slide Decks
- Adding Chrome Custom Search for CFDocs
- Disable Flash Remoting on ColdFusion Servers
- HackMyCF Adds SSL/TLS Scanner
- IncompatibleClassChangeError after ColdFusion 11 Update 5