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 <[EMAIL PROTECTED]> Sent: Thursday, January 04, 2007 8:54 AM To: mysql@lists.mysql.com, [EMAIL PROTECTED] 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
