MySQL FULLTEXT Indexing and Searching

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

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

Trackbacks

Trackback Address: 477/79152EEC66AF7FB2B23F5C39239F7C62

Comments

On 05/25/2006 at 1:08:38 PM UTC www.d06.net wrote:
1
hmmm. thanks...

On 05/25/2006 at 1:09:09 PM UTC www.d06.net wrote:
2
hmmm. thanks...

On 06/26/2006 at 6:26:50 AM UTC Mihaela Ozhan wrote:
3
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.

On 11/21/2006 at 7:30:12 AM UTC Michael Brennan-White wrote:
4
Pete,

Thanks for this article. I was going crazy trying to figure out why I could do a search for WalMart but not CVS.

On 01/13/2007 at 3:05:26 AM UTC Sam wrote:
5
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

On 03/13/2007 at 12:59:16 AM UTC Lucky Modiba wrote:
6
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

On 03/13/2007 at 12:59:42 AM UTC Lucky Modiba wrote:
7
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

On 04/10/2007 at 3:15:40 PM UTC Alaweb wrote:
8
I have met the same problem, I have activated the fulltext indexing option, and run a simple test query, but no result ...

On 05/04/2007 at 9:26:59 AM UTC me wrote:
9
me too .. :(

On 07/04/2007 at 10:29:18 PM UTC madhu wrote:
10
how can we highlight the search result in php.plsss tel me the code

On 01/14/2008 at 7:38:51 AM UTC Michael wrote:
11
Stop this foolishness. Look into Sphinx project, which is orders of magnitude better and faster, with only a bit more pain to set up.

On 01/17/2008 at 10:40:37 PM UTC AMIT wrote:
12
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

On 07/31/2008 at 11:30:36 AM UTC Bruce wrote:
13
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

On 10/25/2008 at 4:17:22 AM UTC tyscko wrote:
14
Congratulations! I used this manual:

http://deckerix.tuxfamily.org/leerArticulo.php?post=92

On 12/11/2008 at 2:44:25 PM UTC Avi Rappoport, SearchTools.com wrote:
15
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.

On 09/01/2009 at 11:09:58 AM UTC index limited wrote:
16
index limited,

Index is limited to a size depending on which sql engine you use

On 10/15/2009 at 2:43:48 PM UTC Fabian Pena wrote:
17
Great article, very useful. thanks cheers http://www.movieteca.com

On 11/27/2009 at 12:20:57 AM UTC rtey wrote:
18
h idssdad dsmdsads dsjksad deew e ijfd fsf ifjdsij

On 12/24/2009 at 9:50:47 AM UTC web wrote:
19
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.

On 07/29/2010 at 7:51:07 AM UTC sainul wrote:
20
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>

On 09/28/2010 at 10:16:59 AM UTC sanju wrote:
21
hey friends, is there any feature in MYSQL to implement full text searches in a language other than English?

On 09/28/2010 at 10:18:09 AM UTC sanju wrote:
22
hey friends, is there any feature in MYSQL to implement full text searches in a language other than English?

On 10/14/2010 at 2:36:43 PM UTC Avi Rappoport, SearchTools.com wrote:
23
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.

On 01/04/2011 at 2:35:15 AM UTC Nilesh wrote:
24
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.

On 03/15/2011 at 8:40:28 AM UTC Prakash wrote:
25
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

On 04/26/2011 at 1:45:23 AM UTC Ram Bahadur Nepal wrote:
26
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" ?

On 08/22/2011 at 4:52:02 AM UTC lexapro online wrote:
27
Really great article with very interesting information. You might want to follow up to this topic!?! 2011

On 08/29/2011 at 3:56:08 AM UTC Sunny wrote:
28
Thanks, Helped me indeed !!

On 02/22/2012 at 7:05:25 PM UTC Mahesh wrote:
29
.net, c++, c# are the keywords.. i have to search using match against. Its not working.

On 01/27/2013 at 9:24:11 PM UTC Samuel wrote:
30
Thanks heaps, It helped a lot!

Post a Comment




  



Spell Checker by Foundeo

Recent Entries



foundeo


did you hack my cf?