fulltext bug with OR

My apologies if this is a known bug, I could not find a list of known
bugs. The detailed description is below, but basically,=20

works: match() against () AND match() against()
nope: match() against () OR match() against()
works: match() against ()>0 AND match() against()
nope: match() against ()>0 AND match() against()>0

where "works" means it uses the fulltext index, "nope" means it doesn't.
I'm using mysql v3.23.49 on Linux/x86. Sorry I have no access to mysql
4.0 in order to test on a more recent version if it was already fixed.
Is this a bug or is this somehow by design?

Thanks,
Matt
---------------------------------------------------
(and now the detailed description:)
How-To-Repeat:
Create a table 'entries', with columns 'author' and 'title', and a
fulltext index on each (individually).

If you issue the queries:

SELECT * from entries where match(author) against ('somebody') and
match(title) against ('something')
SELECT * from entries where match(author) against ('somebody') and
match(author) against ('somebodyelse')

Then it uses the index as expected. But if I issue

SELECT * from entries where match(author) against ('somebody') or
match(title) against ('something')
SELECT * from entries where match(author) against ('somebody') or
match(author) against ('somebodyelse')

(or instead of and)
Then it doesn't. EXPLAIN says:
+------------+------+---------------+------+---------+------ +--------+--
----------+
| table | type | possible_keys | key | key_len | ref | rows |
Extra |
+------------+------+---------------+------+---------+------ +--------+--
----------+
| entries | ALL | NULL | NULL | NULL | NULL | 483226 |
where used |
+------------+------+---------------+------+---------+------ +--------+--
----------+
1 row in set (0.00 sec)
for the latter of the two.

Similarly,=20

SELECT * from entries where match(author) against ('somebody')>0 and
match(author) against('somebody2');
does use the index, while
SELECT * from entries where match(author) against ('somebody')>0 and
match(author) against('somebody2')>0;
does not!







------------------------------------------------------------ ---------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)

To request this thread, e-mail
To unsubscribe, e-mail

Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php


------------------------------------------------------------ ---------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)

To request this thread, e-mail bugs-thread12891@lists.mysql.com
To unsubscribe, e-mail

Re: fulltext bug with OR

Hi!

On Nov 01, Matthew Richardson wrote:
> My apologies if this is a known bug, I could not find a list of known
> bugs. The detailed description is below, but basically,
>
> works: match() against () AND match() against()
> nope: match() against () OR match() against()

This is not a bug, it's a missing feature.
MySQL cannot use index in "expr OR expr" like query.
(it's not completely true, it can use normal index, if both expr can be
resolved via the same index, but fulltext searches are too complex even
for this). And you don't actualy need it, just use

SELECT * from entries where match(author) against ('somebody something')

instead of

SELECT * from entries where match(author) against ('somebody') or
match(title) against ('something')

> works: match() against ()>0 AND match() against()
> nope: match() against ()>0 AND match() against()>0

This is, again, missing feature, and workaround is simple

match() against ()>0 is completely identical to match() against()
so just drop ">0"

Regards,
Sergei

--
MySQL Development Team
__ ___ ___ ____ __
/ |/ /_ __/ __/ __ \/ / Sergei Golubchik
/ /|_/ / // /\ \/ /_/ / /__ MySQL AB, http://www.mysql.com/
/_/ /_/\_, /___/\___\_\___/ Osnabrueck, Germany
<___/

------------------------------------------------------------ ---------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)

To request this thread, e-mail bugs-thread12893@lists.mysql.com
To unsubscribe, e-mail

Re: fulltext bug with OR

Hi!

On Nov 02, Matthew Richardson wrote:
> Thanks for the reply Sergei,
>
> Unfortunately, I can't just do
>
> SELECT * from entries where match(author) against ('somebody something')
>
> The reason being that I am actually doing complex queries, like
> ( 'somebody' and 'something' ) or 'nobody'
> e.g.
> (match() against('somebody') AND match() against ('something')) or
> match() against('nobody')

oops

> Also, I don't know if this this is planned as part of the fulltext
> improvements, but I think it would be great to have another kind of
> index ("word"?) and query (maybe "hasword") which indexes every word (no
> stop-words) and returns *all* entries that contain the word in the query
> (even if the word is in > 50% of the entries) (this may require count()
> to be quick in order to actually be useful).

in MySQL 4.0.2+ there is fulltext search IN BOOLEAN MODE (cf. the
manual). It's faster than old - natural-language - fulltext search,
and it doesn't have 50% limit. It is still a subject to stopword
removel, but it will be changed. Also, IN BOOLEAN MODE you can do
searches like ('somebody' and 'something') or 'nobody' natively,
you example could be rewritten as
MATCH ... AGAINST ("(+somebody +something) nobody" IN BOOLEAN MODE)

Regards,
Sergei

--
MySQL Development Team
__ ___ ___ ____ __
/ |/ /_ __/ __/ __ \/ / Sergei Golubchik
/ /|_/ / // /\ \/ /_/ / /__ MySQL AB, http://www.mysql.com/
/_/ /_/\_, /___/\___\_\___/ Osnabrueck, Germany
<___/

------------------------------------------------------------ ---------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)

To request this thread, e-mail bugs-thread12914@lists.mysql.com
To unsubscribe, e-mail