re: Fulltext problem

[J.R. Bullington]

Hi Devy --

There are a couple of issues with your query below, and hopefully we can help you figure
it out.

First off, your table and query structure are fine. However, one can ask why not use a
TINYTEXT or even a TEXT field instead of VARCHAR(255). It's all in the memory overhead.
=) 

Moving on, it's not that you don't have a relevance, it's that you don't have enough
records in your database to compare the MATCH() to. Read
http://dev.mysql.com/doc/refman/5.0/en/fulltext-search.html for more information, but pay
particular attention to the last 4 paragraphs.

Your search text must match at most 49% of the rows in the database, or a 0 relevancy
will appear. Since, in your test case, it matches 100%, you won't get a relevance. Try
adding 5-6 more records to your table and then do a search with MySQL in the field1.

Try this:

CREATE TABLE as below.

INSERT INTO ft_test (field1,field2,field3) VALUES
('mysql full text', 'this is a test', 'mysql fulltext'),
('Email Tutorial','DBMS stands for DataBase ...','hi mom'),
('How To Use Yahoo Well','After you went through a ...','hi dad'),
('Optimizing your databases','In this tutorial we will show ...','hi sis'),
('1001 MySQL Tricks','1. Never run mysqld as root. 2. ...','hi bro'),
('MSSQL vs. YourSQL','In the following database comparison ...','hi uncle'),
('SQL Injection Security','When configured properly, MySQL ...','hi aunt'); 

Then, when you run your query, "SELECT *, MATCH(field1) AGAINST('mysql') as relevancy
FROM ft_test;"  you get

id, field1, field2, field3, relevance
1, 'mysql full text', 'this is a test', 'mysql fulltext', 0.88573294878006
2, 'Email Tutorial', 'DBMS stands for DataBase ...', 'hi mom', 0
3, 'How To Use Yahoo Well', 'After you went through a ...', 'hi dad', 0
4, 'Optimizing your databases', 'In this tutorial we will show ...', 'hi sis', 0
5, '1001 MySQL Tricks', '1. Never run mysqld as root. 2. ...', 'hi bro',
0.88573294878006
6, 'MSSQL vs. YourSQL', 'In the following database comparison ...', 'hi uncle', 0
7, 'SQL Injection Security', 'When configured properly, MySQL ...', 'hi aunt', 0 

(sorry about the spacing..., thanks to MySQL manual for the source of inserts)

Notice that in the last column, the relevance is 0.88 in two of the fields, as MySQL is
in those fields and no other.

Then you can try other search terms in other fields, such as "SELECT *, MATCH(field2)
AGAINST('database') as relevancy FROM ft_test; ". When looking at the manual for the
FULLTEXT searches, also make sure to pay attention to the STOPWORDS section, IGNORED
WORDS section, and the ft_min_word  and ft_max_word . The default minimum word length for
FULLTEXT searches is 4, unless you change it in the VARIABLES.

HTH!
J.R.

----------------------------------------
From: devy <devyer@stripped>
Sent: Thursday, January 04, 2007 8:54 AM
To: mysql@stripped, internals@stripped
Subject: Fulltext problem 

Hi,
today I've experienced a problem that I don't understand and I can't solve!

I've created a table as follows:
---------------
CREATE TABLE `ft_test` (
`id` int(11) NOT NULL auto_increment,
`field1` varchar(255) NOT NULL ,
`field2` varchar(255) NOT NULL ,
`field3` text NOT NULL,
PRIMARY KEY (`id`),
FULLTEXT KEY `field1` (`field1`),
FULLTEXT KEY `field2` (`field2`),
FULLTEXT KEY `field3` (`field3`),
FULLTEXT KEY `f1d2` (`field1`,`field2`)
) ENGINE=MyISAM CHARSET=utf8;
-----------------

and then I've filled this table with the following statement:
-----------------
insert into ft_test (field1,field2,field3)
VALUES('mysql full text', 'this is a test', 'mysql fulltext');
-----------------

the problem is that when I execute this query I always get 0 as relevance:
> select *, match(field1) against('mysql') as relevancy from ft_test;
id field1 field2 field3 relevancy
------ --------------- -------------- -------------- ---------
1 mysql full text this is a test mysql fulltext 0

I expected a value for relevancy! shouldn't I?
I tested with all other words and combination of fulltext index:
---
select *, match(field2) against('mysql') as relevancy from ft_test;
select *, match(field3) against('mysql') as relevancy from ft_test;
select *, match(field3) against('fulltext') as relevancy from ft_test;
---

but I always get 0!

This is a "show variables" of my mysql server
Variable_name Value
------------------------ --------------
version 5.0.18-nt
ft_boolean_syntax + -><()~*:""&|
ft_max_word_len 84
ft_min_word_len 4
ft_query_expansion_limit 20
ft_stopword_file (built-in)

Any advice?

Thanks