MATCH ... AGAINST not working as expected

#1

Per the MySQL online docs, the statement below:

SELECT * FROM `table` WHERE MATCH (Description) AGAINST ('+party +fun
+town' IN BOOLEAN MODE)

is supposed to return rows where ALL THREE words are present. Instead
it is returning all rows where all three words are present as well as
rows where 2 out of the three words are present.

Is my understanding of how this is supposed to work correct?

#2

El 16/01/2011 1:19, EastSideDev escribió/wrote:

Quote:

Per the MySQL online docs, the statement below:

SELECT * FROM `table` WHERE MATCH (Description) AGAINST ('+party +fun
+town' IN BOOLEAN MODE)

is supposed to return rows where ALL THREE words are present. Instead
it is returning all rows where all three words are present as well as
rows where 2 out of the three words are present.

Is my understanding of how this is supposed to work correct?

"fun" is probably being ignored because of its 3 char length:

http://dev.mysql.com/doc/refman/5.0/...-language.html

«Some words are ignored in full-text searches:

* Any word that is too short is ignored. The default minimum length of
words that are found by full-text searches is four characters.

*Words in the stopword list are ignored. A stopword is a word such as
“the” or “some” that is so common that it is considered to have zero
semantic value. There is a built-in stopword list, but it can be
overwritten by a user-defined list.»

--
-- http://alvaro.es - Álvaro G. Vicario - Burgos, Spain
-- Mi sitio sobre programación web: http://borrame.com
-- Mi web de humor satinado: http://www.demogracia.com
--

#3

"Álvaro G. Vicario":

Quote:

El 16/01/2011 1:19, EastSideDev escribió/wrote:

SELECT * FROM `table` WHERE MATCH (Description) AGAINST ('+party
+fun +town' IN BOOLEAN MODE)

"fun" is probably being ignored because of its 3 char length:
* Any word that is too short is ignored. The default minimum length
of words that are found by full-text searches is four characters.

*Words in the stopword list are ignored. A stopword is a word such as
“the” or “some” that is so common that ...»

If "the" is ignored because it has only three characters, why is it on
the default stopword list?

--
Erick

#4

On 17-01-11 09:27, Erick T. Barkhuis wrote:

Quote:

"Álvaro G. Vicario":

El 16/01/2011 1:19, EastSideDev escribió/wrote:

SELECT * FROM `table` WHERE MATCH (Description) AGAINST ('+party
+fun +town' IN BOOLEAN MODE)

"fun" is probably being ignored because of its 3 char length:
* Any word that is too short is ignored. The default minimum length
of words that are found by full-text searches is four characters.

*Words in the stopword list are ignored. A stopword is a word such as
“the” or “some” that is so common that ...»

If "the" is ignored because it has only three characters, why is it on
the default stopword list?

Because the minimum word length can be changed, from the default value 4
to a shorter value...

http://dev.mysql.com/doc/refman/5.0/...t_min_word_len

--
Luuk

#5

Luuk:

Quote:

On 17-01-11 09:27, Erick T. Barkhuis wrote:
"Álvaro G. Vicario":

El 16/01/2011 1:19, EastSideDev escribió/wrote:

SELECT * FROM `table` WHERE MATCH (Description) AGAINST
('+party +fun +town' IN BOOLEAN MODE)

"fun" is probably being ignored because of its 3 char length:
* Any word that is too short is ignored. The default minimum
length of words that are found by full-text searches is four
characters.

*Words in the stopword list are ignored. A stopword is a word
such as “the” or “some” that is so common that ...»

If "the" is ignored because it has only three characters, why is
it on the default stopword list?

Because the minimum word length can be changed, from the default
value 4 to a shorter value...

http://dev.mysql.com/doc/refman/5.0/...t_min_word_len

Yes, but so can the stopword list. So, _if_ someone is smart enough to
reduce the default word length, he should include all
three-letter-words that he wants to ignore into the stopword list at
that moment.

I was just wondering: if MySQL has defaults for the minimum word length
and the stopword list, why would these defaults contain redundant
values?
Defaults can be changed, and the user is responsible for correct values
in both. But the _default_ needs not to contain redundant values, does
it?

--
Erick

#6

On 17-01-11 09:44, Erick T. Barkhuis wrote:

Quote:

Luuk:

On 17-01-11 09:27, Erick T. Barkhuis wrote:
"Álvaro G. Vicario":

El 16/01/2011 1:19, EastSideDev escribió/wrote:

SELECT * FROM `table` WHERE MATCH (Description) AGAINST
('+party +fun +town' IN BOOLEAN MODE)

"fun" is probably being ignored because of its 3 char length:
* Any word that is too short is ignored. The default minimum
length of words that are found by full-text searches is four
characters.

*Words in the stopword list are ignored. A stopword is a word
such as “the” or “some” that is so common that ...»

If "the" is ignored because it has only three characters, why is
it on the default stopword list?

Because the minimum word length can be changed, from the default
value 4 to a shorter value...

http://dev.mysql.com/doc/refman/5.0/...t_min_word_len

Yes, but so can the stopword list. So, _if_ someone is smart enough to
reduce the default word length, he should include all
three-letter-words that he wants to ignore into the stopword list at
that moment.

I was just wondering: if MySQL has defaults for the minimum word length
and the stopword list, why would these defaults contain redundant
values?
Defaults can be changed, and the user is responsible for correct values
in both. But the _default_ needs not to contain redundant values, does
it?

Indeed, it does not need to contain them,

But it also includes i.e. 'wherein', and that word is never used by me,
so its redundant to me as well....

http://dev.mysql.com/doc/refman/5.0/...stopwords.html

--
Luuk

#7

El 17/01/2011 9:27, Erick T. Barkhuis escribió/wrote:

Quote:

"Álvaro G. Vicario":

El 16/01/2011 1:19, EastSideDev escribió/wrote:

SELECT * FROM `table` WHERE MATCH (Description) AGAINST ('+party
+fun +town' IN BOOLEAN MODE)

"fun" is probably being ignored because of its 3 char length:
* Any word that is too short is ignored. The default minimum length
of words that are found by full-text searches is four characters.

*Words in the stopword list are ignored. A stopword is a word such as
“the” or “some” that is so common that ...»

If "the" is ignored because it has only three characters, why is it on
the default stopword list?

The OP's example illustrates this just fine: he'll probably consider to
lower the character length so he's able to find "fun", but I don't think
he's specially interested in "the".

They're just similar concepts that MySQL allows to be handled separately.

--
-- http://alvaro.es - Álvaro G. Vicario - Burgos, Spain
-- Mi sitio sobre programación web: http://borrame.com
-- Mi web de humor satinado: http://www.demogracia.com
--

#8

"Álvaro G. Vicario":

Quote:

El 17/01/2011 9:27, Erick T. Barkhuis escribió/wrote:

If "the" is ignored because it has only three characters, why is it
on the default stopword list?

The OP's example illustrates this just fine: he'll probably consider
to lower the character length so he's able to find "fun", but I don't
think he's specially interested in "the".

So, he doesn't include "the" in his search phrase.

Quote:

They're just similar concepts that MySQL allows to be handled
separately.

In a way, I understand this.
Yet, if someone isn't interested in stopwords, why would [s]he include
them in the query?

%puzzled%

--
Erick

#9

On Jan 17, 10:29*am, "Erick T. Barkhuis" <erick.use-... (AT) ardane (DOT) c.o.m>
wrote:

Quote:

In a way, I understand this.
Yet, if someone isn't interested in stopwords, why would [s]he include
them in the query?

%puzzled%
Because generally the words in the query are supplied by a web site

user typing into a search box and the decision of what words should be
searchable are the programmer's.

#10

Captain Paralytic:

Quote:

On Jan 17, 10:29*am, "Erick T. Barkhuis" <erick.use-... (AT) ardane (DOT) c.o.m
wrote:
In a way, I understand this.
Yet, if someone isn't interested in stopwords, why would [s]he
include them in the query?

%puzzled%
Because generally the words in the query are supplied by a web site
user typing into a search box and the decision of what words should be
searchable are the programmer's.

That will be the reason, yes.

(Still strange: if I were a user, typing in "some" or "whereas" -
knowing that those words are in the text somewhere - I would frown if
the application said that these words are not included, assuming they
are in the stopwords list.

But OK, at some point, somewhere, this feature may be useful.)

--
Erick