From: Date: January 4 2007 4:47pm
Subject: re: Fulltext problem
List-Archive: http://lists.mysql.com/mysql/204198
Message-Id: <75ed053618e441f28fcf21703a9725eb@innovatim.com>
MIME-Version: 1.0
Content-Type: multipart/alternative;
	boundary=----_SmarterMail_NextPart_8373718227611102

------_SmarterMail_NextPart_8373718227611102
Content-Type: text/plain;
	charset=iso-8859-1
Content-Transfer-Encoding: quoted-printable

Hi Devy --=0D=0A=0D=0AThere are a couple of issues with your query below, a=
nd hopefully we can help you figure it out.=0D=0A=0D=0AFirst off, your tabl=
e 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 overh=
ead. =3D) =0D=0A=0D=0AMoving 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 MAT=
CH() to. Read http://dev.mysql.com/doc/refman/5.0/en/fulltext-search.html f=
or more information, but pay particular attention to the last 4 paragraphs.=
=0D=0A=0D=0AYour search text must match at most 49% of the rows in the data=
base, or a 0 relevancy will appear. Since, in your test case, it matches 10=
0%, you won't get a relevance. Try adding 5-6 more records to your table an=
d then do a search with MySQL in the field1.=0D=0A=0D=0ATry this:=0D=0A=0D=
=0ACREATE TABLE as below.=0D=0A=0D=0AINSERT INTO ft_test (field1,field2,fie=
ld3) VALUES=0D=0A('mysql full text', 'this is a test', 'mysql fulltext'),=
=0D=0A('Email Tutorial','DBMS stands for DataBase ...','hi mom'),=0D=0A('Ho=
w To Use Yahoo Well','After you went through a ...','hi dad'),=0D=0A('Optim=
izing your databases','In this tutorial we will show ...','hi sis'),=0D=0A(=
'1001 MySQL Tricks','1. Never run mysqld as root. 2. ...','hi bro'),=0D=0A(=
'MSSQL vs. YourSQL','In the following database comparison ...','hi uncle'),=
=0D=0A('SQL Injection Security','When configured properly, MySQL ...','hi a=
unt'); =0D=0A=0D=0AThen, when you run your query, "SELECT *, MATCH(field1) =
AGAINST('mysql') as relevancy FROM ft_test;"  you get=0D=0A=0D=0Aid, field1=
, field2, field3, relevance=0D=0A1, 'mysql full text', 'this is a test', 'm=
ysql fulltext', 0.88573294878006=0D=0A2, 'Email Tutorial', 'DBMS stands for=
 DataBase ...', 'hi mom', 0=0D=0A3, 'How To Use Yahoo Well', 'After you wen=
t through a ...', 'hi dad', 0=0D=0A4, 'Optimizing your databases', 'In this=
 tutorial we will show ...', 'hi sis', 0=0D=0A5, '1001 MySQL Tricks', '1. N=
ever run mysqld as root. 2. ...', 'hi bro', 0.88573294878006=0D=0A6, 'MSSQL=
 vs. YourSQL', 'In the following database comparison ...', 'hi uncle', 0=0D=
=0A7, 'SQL Injection Security', 'When configured properly, MySQL ...', 'hi =
aunt', 0 =0D=0A=0D=0A(sorry about the spacing..., thanks to MySQL manual fo=
r the source of inserts)=0D=0A=0D=0ANotice that in the last column, the rel=
evance is 0.88 in two of the fields, as MySQL is in those fields and no oth=
er.=0D=0A=0D=0AThen 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 pa=
y attention to the STOPWORDS section, IGNORED WORDS section, and the ft_min=
_word  and ft_max_word . The default minimum word length for FULLTEXT searc=
hes is 4, unless you change it in the VARIABLES.=0D=0A=0D=0AHTH!=0D=0AJ.R.=
=0D=0A=0D=0A----------------------------------------=0D=0AFrom: devy <devye=
r@stripped>=0D=0ASent: Thursday, January 04, 2007 8:54 AM=0D=0ATo: mysql@l=
ists.mysql.com, internals@stripped=0D=0ASubject: Fulltext problem =
=0D=0A=0D=0AHi,=0D=0Atoday I've experienced a problem that I don't understa=
nd and I can't solve!=0D=0A=0D=0AI've created a table as follows:=0D=0A----=
-----------=0D=0ACREATE TABLE `ft_test` (=0D=0A`id` int(11) NOT NULL auto_i=
ncrement,=0D=0A`field1` varchar(255) NOT NULL ,=0D=0A`field2` varchar(255) =
NOT NULL ,=0D=0A`field3` text NOT NULL,=0D=0APRIMARY KEY (`id`),=0D=0AFULLT=
EXT KEY `field1` (`field1`),=0D=0AFULLTEXT KEY `field2` (`field2`),=0D=0AFU=
LLTEXT KEY `field3` (`field3`),=0D=0AFULLTEXT KEY `f1d2` (`field1`,`field2`=
)=0D=0A) ENGINE=3DMyISAM CHARSET=3Dutf8;=0D=0A-----------------=0D=0A=0D=0A=
and then I've filled this table with the following statement:=0D=0A--------=
---------=0D=0Ainsert into ft_test (field1,field2,field3)=0D=0AVALUES('mysq=
l full text', 'this is a test', 'mysql fulltext');=0D=0A-----------------=
=0D=0A=0D=0Athe problem is that when I execute this query I always get 0 as=
 relevance:=0D=0A> select *, match(field1) against('mysql') as relevancy fr=
om ft_test;=0D=0Aid field1 field2 field3 relevancy=0D=0A------ ------------=
--- -------------- -------------- ---------=0D=0A1 mysql full text this is =
a test mysql fulltext 0=0D=0A=0D=0AI expected a value for relevancy! should=
n't I?=0D=0AI tested with all other words and combination of fulltext index=
:=0D=0A---=0D=0Aselect *, match(field2) against('mysql') as relevancy from =
ft_test;=0D=0Aselect *, match(field3) against('mysql') as relevancy from ft=
_test;=0D=0Aselect *, match(field3) against('fulltext') as relevancy from f=
t_test;=0D=0A---=0D=0A=0D=0Abut I always get 0!=0D=0A=0D=0AThis is a "show =
variables" of my mysql server=0D=0AVariable_name Value=0D=0A---------------=
--------- --------------=0D=0Aversion 5.0.18-nt=0D=0Aft_boolean_syntax + ->=
<()~*:""&|=0D=0Aft_max_word_len 84=0D=0Aft_min_word_len 4=0D=0Aft_query_exp=
ansion_limit 20=0D=0Aft_stopword_file (built-in)=0D=0A=0D=0AAny advice?=0D=
=0A=0D=0AThanks=0D=0A=0D=0A

------_SmarterMail_NextPart_8373718227611102--