Pete Freitag Pete Freitag

MySQL FULLTEXT Indexing and Searching

Published on September 29, 2005
By Pete Freitag
databases

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?



mysql fulltext indexing search databases sql

MySQL FULLTEXT Indexing and Searching was first published on September 29, 2005.

If you like reading about mysql, fulltext, indexing, search, databases, or sql then you might also like:

Discuss / Follow me on Twitter ↯

Comments

hmmm. thanks...
by www.d06.net on 05/25/2006 at 1:08:38 PM UTC
hmmm. thanks...
by www.d06.net on 05/25/2006 at 1:09:09 PM UTC
Never forget-Arkadas means friendship in Turkish. Arkadas Real Estate Agents will help you find your dream holiday property in Turkey and will also become your friends-ready to help you when you need it. Choose Arkadas Real Estate for hassle-free holiday home ownership for your Kusadasi investment.
by Mihaela Ozhan on 06/26/2006 at 6:26:50 AM UTC
Pete,

Thanks for this article. I was going crazy trying to figure out why I could do a search for WalMart but not CVS.
by Michael Brennan-White on 11/21/2006 at 7:30:12 AM UTC
how can we highlight the search result in php.plsss tel me the code
by madhu on 07/04/2007 at 10:29:18 PM UTC
Stop this foolishness. Look into Sphinx project, which is orders of magnitude better and faster, with only a bit more pain to set up.
by Michael on 01/14/2008 at 7:38:51 AM UTC
hi all,

i just want to know whether it is useful to alter the 50% threshold scheme that mysql uses by default ,is it beneficial in any way regarding serach(either by increasing or decreasing its value)or is it good to use default value of 50% only.

which is better (altering the file to change 50% value or using default 50 %)

thanx and regards
by AMIT on 01/17/2008 at 10:40:37 PM UTC
This tutorial is simple and straight forword, i plan to use it on my site http://www.spiralteck.com .I wish all tutorials could be this simple.
by web on 12/24/2009 at 9:50:47 AM UTC
I have one error

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

<html>
<head>
<body>

<form name="form" action="search2.php" method="get">
<input type="text" name="q" />
<input type="submit" name="Submit" value="Search" />
</form>

<?php





$var = @$_GET['q'] ;
$trimmed = trim($var);
$quary = "'%".$trimmed."%'";
$select = "SELECT *,
MATCH(news) AGAINST('$trimmed') AS score
FROM articles
WHERE MATCH(news) AGAINST('$trimmed')
ORDER BY score DESC";
$con =mysql_connect("localhost","search","123456");
if(!con)
{
die ("Database not connected");
}
else
{
mysql_select_db ("omassery", $con);

echo $result = mysql_query ($select, $con);
}
while ($row = mysql_fetch_array ($result))
{
echo $row['news']."<br>";
}
?>

</body>
</html>
by sainul on 07/29/2010 at 7:51:07 AM UTC
Thanks for post this article but i have an issue please explain with complete example with complete search. Indexing what roll play in search and how to use please active it asap am a waiting.
by Nilesh on 01/04/2011 at 2:35:15 AM UTC
how would we find related search keywords? For ex: search keyword is 'toyota', it shows results included where it's included "toyota,toyota corolla, toyota handbook" etc. And how would we show a bunch of related search keywords "toyota corolla","toyota handbook" ?
by Ram Bahadur Nepal on 04/26/2011 at 1:45:23 AM UTC
Thanks,
Helped me indeed !!
by Sunny on 08/29/2011 at 3:56:08 AM UTC
Thanks heaps, It helped a lot!
by Samuel on 01/27/2013 at 9:24:11 PM UTC