MySQL FULLTEXT Indexing and Searching

September 29, 2005
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?



Related Entries

48 people found this page useful, what do you think?

Comments

hmmm. thanks...
hmmm. thanks...
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.
Pete, Thanks for this article. I was going crazy trying to figure out why I could do a search for WalMart but not CVS.
Hello Pete, Thanks for the valuable information about Full text searching ability of MySQL. By the way, do you know how to implement search queries like "All words", "Any Words" & Exact Match like there are in PHPKB Knowledge Base Software at http://www.knowledgebase-script.com
Thanx for the article but I seem to be having a problem with my fulltext search SELECT job_id FROM cv_jobs where MATCH (job_description,job_title) AGAINST ('Plant Accountant') AND (job_status_id = 1) the query does not bring back any results even if the value in the against exists and the status is 1 what could be the cause of it. thanx
Thanx for the article but I seem to be having a problem with my fulltext search SELECT job_id FROM cv_jobs where MATCH (job_description,job_title) AGAINST ('Plant Accountant') AND (job_status_id = 1) the query does not bring back any results even if the value in the against exists and the status is 1 what could be the cause of it. thanx
I have met the same problem, I have activated the fulltext indexing option, and run a simple test query, but no result ...
me too .. :(
how can we highlight the search result in php.plsss tel me the code
Stop this foolishness. Look into Sphinx project, which is orders of magnitude better and faster, with only a bit more pain to set up.
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
Hello, Are there any pointers to the performance of this Full Text search in mysql? I have a table that stores short text messages (upto 4000 chars), and this table grows fast, about 15 mil/day. I wonder if I should try the full text search in mysql or other ? Thanks. Bruce
Congratulations! I used this manual: http://deckerix.tuxfamily.org/leerArticulo.php?post=92
I've just looked into this text search, and I would call it primitive at best. It simply will not perform in a user-friendly way. People expect to find shorter words, like the above "CVS" -- all modern search engines support that. Likewise the arbitrary list of stopwords and ignoring any word in 50% => of the results has no basis in usability research. It's just what the IR guys thought would be good back when disk space was really expensive. Frankly, canning this entirely and sending people to Solr (Lucene) or Sphinx Search would be a much better idea. Even programmers need decent search.
index limited, Index is limited to a size depending on which sql engine you use
Great article, very useful.
thanks
cheers
http://www.movieteca.com
h idssdad dsmdsads dsjksad deew e ijfd fsf ifjdsij
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.
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>
hey friends, is there any feature in MYSQL to implement full text searches in a language other than English?
hey friends, is there any feature in MYSQL to implement full text searches in a language other than English?
sanju, text search works reasonably well with many languages, because it's just string matching. I don't think MySQL search has anything special for language or character set recognition, so forget anything like stemming here.
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.
This example is very helpful for relevant search results list down

Ex: if we are search for blue t-shirt, the query displays all the blue t-shirt’s and t-shirt’s keyword
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" ?
Really great article with very interesting information. You might want to follow up to this topic!?! 2011
Thanks,
Helped me indeed !!
.net, c++, c# are the keywords.. i have to search using match against. Its not working.
Thanks heaps, It helped a lot!

Post a Comment




  



Spell Checker by Foundeo

Recent Entries



foundeo


did you hack my cf?