Google Answers Logo
View Question
 
Q: Fulltext queries in MySQL ( No Answer,   1 Comment )
Question  
Subject: Fulltext queries in MySQL
Category: Computers > Programming
Asked by: rhinst-ga
List Price: $10.00
Posted: 26 Aug 2006 20:32 PDT
Expires: 25 Sep 2006 20:32 PDT
Question ID: 759831
I am running mysql server version 5.0.24 on debian sarge. I have a
table that was created as follows:

create table articles(id int(11) unsigned not null auto_increment,
title varchar(255) not null default '', body text not null default '',
date int(11) unsigned not null default 0, primary key (id),
fulltext(title,body));

I then inserted a row of data with the following query:

insert into articles values (NULL, 'this is an article about
penguins', 'The number of penguin species has been and still is a
matter of debate. The numbers of penguin species listed in the
literature vary between 16 and 19 species. Some sources consider the
White-Flippered Penguin a separate Eudyptula species, although today
it is generally considered a subspecies of the Little Penguin (e.g.
Williams, 1995; Davis & Renner, 2003). Similarly, it is still unclear
whether the Royal Penguin is merely a colour morph of the Macaroni
penguin. Also possibly eligible to be treated as a separate species is
the Northern population of Rockhopper penguins (Davis & Renner, 2003).
Although all penguin species are native to the southern hemisphere,
they are not, contrary to popular belief, found only in cold climates,
such as Antarctica. In fact, only a few species of penguin actually
live so far south. Three species live in the tropics; one lives as far
north as the Galápagos Islands (the Galápagos Penguin) and will
occasionally cross the equator while feeding.', 0);

When i run a fulltext search, such as the following:

select match(title,body) against ('penguin') from articles;

I get zero relevance for the article, even though it mentions the word
penuin several times:

+---------------------------------------+
| match(title,body) against ('penguin') |
+---------------------------------------+
|                                     0 |
+---------------------------------------+


Here are the fulltext-related variables i get from "show variables":

+--------------------------+----------------+
| Variable_name            | Value          |
+--------------------------+----------------+
| ft_boolean_syntax        | + -><()~*:""&| |
| ft_max_word_len          | 84             |
| ft_min_word_len          | 4              |
| ft_query_expansion_limit | 20             |
| ft_stopword_file         | (built-in)     |
+--------------------------+----------------+


Anyone have any idea what might be causing this strange behavior? I
have another server using the same versions of mysql and debian, and
it works fine.
Answer  
There is no answer at this time.

Comments  
Subject: Re: Fulltext queries in MySQL
From: snoyes-ga on 28 Aug 2006 13:53 PDT
 
Is this the only row in the table? You don't get fulltext matches
until you have at least 3 rows, because any word that matches 50% or
more of the rows is ignored as noise.

Important Disclaimer: Answers and comments provided on Google Answers are general information, and are not intended to substitute for informed professional medical, psychiatric, psychological, tax, legal, investment, accounting, or other professional advice. Google does not endorse, and expressly disclaims liability for any product, manufacturer, distributor, service or service provider mentioned or any opinion expressed in answers or comments. Please read carefully the Google Answers Terms of Service.

If you feel that you have found inappropriate content, please let us know by emailing us at answers-support@google.com with the question ID listed above. Thank you.
Search Google Answers for
Google Answers  


Google Home - Answers FAQ - Terms of Service - Privacy Policy