Re: Fulltext problem
devy wrote:
---cut---
-
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:
---cut---
A FULLTEXT search will not match return values that are within 50% of
the rows (or, index them) - this would just lead to returning too many
hits and make relevance not-so-relevant ;)
As you are only inserting one row, the this is certainly 50% of the
rows ;)
Add more rows, then re-run your test, keeping in mind the above.
Cheers,
Mark
--
Mark Leith, Support Engineer
MySQL AB, Worcester, England, www.mysql.com
Are you MySQL certified? www.mysql.com/certification
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
re: Fulltext problem
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
RE: Fulltext problem
With so few rows, are you hitting the 50% rule? Try again with a decent
number of rows.
-Original Message-
From: devy [mailto:[EMAIL PROTECTED]
Sent: Thursday, January 04, 2007 5:46 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
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Fulltext problem with '.'
Off the top of me balding head I can think of two things... One is just leave the dot out and add it in later. Just look for com or couk netetc I also know there is a way of telling SQL that . it a char by putting it in some kind of but I can not remember how I hope this helps a bit... Simon -Original Message- From: Martin Kjeldsen [mailto:[EMAIL PROTECTED]] Sent: 15 August 2001 11:41 To: [EMAIL PROTECTED] Subject: Fulltext problem with '.' Hi, if you use the fulltext to search for words like internet.com my search allways fails. The reason for is as far as I know that the dot (.) is used by the parser as a word separator at least when making the index. The easy way to solve this would be to split the query in 2 words internet AND com, but here is the problem that both com and internet exists in more than 50% of the rows. Offcourse you could search like field LIKE '%internet.com'% and this will work, but then again there was reasons for changing to the fulltext index (speed, relevance and so on). Is there a way to solve this, can it be solve and will it be solved? Martin Kjeldsen - 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 [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] 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 [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
