Bug #27810 fulltext search hazardous

• Submitted: 13 Apr 2007 14:43
• Modified: 13 Apr 2007 15:50
• Reporter: Cedric Billiet
• Status: Closed
• Category: MySQL Server
• Severity: S3 (Non-critical)
• Version: 4.1.9 & 3.23
• OS: Any (winxp & redhat)
• CPU Architecture: Any
• Tags: fulltext

[13 Apr 2007 14:43] Cedric Billiet

Description:
I tried an example given id Mysql doc about fulltext search.
It worked well, but when I delete some lines it does not return anything anymore.
I don't know exactly what is behind but I tried both on easyphp 1.8, with mysql 4.1.9-max with latin1 charachter set (latin1_general_ci) on a winxp, and on my server (redhat with mysql 3.23, same charachter set) and the same hazardous returns occured.
The example below is the one given in Doc

How to repeat:
This return two lines:

CREATE TABLE articles (
id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
title VARCHAR(200),
body TEXT,
FULLTEXT (title,body)
);

INSERT INTO articles (title,body) VALUES
('MySQL Tutorial','DBMS stands for DataBase ...'),
('How To Use MySQL Well','After you went through a ...'),
('Optimizing MySQL','In this tutorial we will show ...'),
('1001 MySQL Tricks','1. Never run mysqld as root. 2. ...'),
('MySQL vs. YourSQL','In the following database comparison ...'),
('MySQL Security','When configured properly, MySQL ...');

SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('database');

And this does not return anything:

CREATE TABLE articles (
id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
title VARCHAR(200),
body TEXT,
FULLTEXT (title,body)
);

INSERT INTO articles (title,body) VALUES
('MySQL Tutorial','DBMS stands for DataBase ...'),
('1001 MySQL Tricks','1. Never run mysqld as root. 2. ...'),
('MySQL vs. YourSQL','In the following database comparison ...'),
('MySQL Security','When configured properly, MySQL ...');

SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('database');

Suggested fix:
Absolutely no idea but ideas would be very appreciated

[13 Apr 2007 15:50] Cedric Billiet

it is not a bug but just a bad conception: fulltext is unusable!

"In mysql, every correct word in the collection and in the query is weighted according to its significance in the collection or query. Consequently, a word that is present in many documents has a lower weight and if a word is rare, it has higher weight. So if a word is present in 50% of the rows in a table, a query searching for that word will result in 0 result. This, mysql terms as relevance. But for me, it resulted in incorrect results for a query."

[13 Apr 2007 19:03] MySQL Verification Team

You can use use FT IN BOOLEAN MODE:

mysql> SELECT * FROM articles WHERE MATCH (title,body)
    -> AGAINST ('database');
Empty set (0.00 sec)

mysql>
mysql>
mysql> SELECT * FROM articles WHERE MATCH (title,body)
    -> AGAINST ('+database' IN BOOLEAN MODE);
+----+-------------------+------------------------------------------+
| id | title             | body                                     |
+----+-------------------+------------------------------------------+
|  1 | MySQL Tutorial    | DBMS stands for DataBase ...             |
|  3 | MySQL vs. YourSQL | In the following database comparison ... |
+----+-------------------+------------------------------------------+
2 rows in set (0.00 sec)

mysql>