I'm just getting started with FULLTEXT indexes for a
table and am seeing some very off-base results. While
it may be something I'm doing, I didn't see anything
in the online manual to indicate why these results
would be so bizarre.
Here is a test table I've created:
create table ft_test
(id smallint unsigned not null auto_increment,
note_text text not null,
fulltext (note_text),
primary key (id));
Once this table is created, I insert a few rows to
test with:
insert into ft_test values
(null, 'this is the first row');
insert into ft_test values
(null, 'this is the second row with more text');
insert into ft_test values
(null, 'this is a fourth row with different text');
insert into ft_test values
(null, 'this is a fifth row for not another
variation');
insert into ft_test values
(null, 'and here is a sixth row that should be
sufficient');
Ok, so far so good (yea, I know I forgot a "third"
row)... but here's where it gets weird. All of the
words I search on below appear in only ONE of the
above rows and all of them are four or more
characters. Despite this, some of them return a
correct result and other rows, which clearly have a
matching word, return an empty set.
The word "different" = empty set?!
mysql> select id from ft_test where match (note_text)
against ('different');
Empty set (0.00 sec)
The word "sufficient" = 1 row
mysql> select id from ft_test where match (note_text)
against ('sufficient');
+----+
| id |
+----+
| 5 |
+----+
1 row in set (0.00 sec)
The word "first" = empty set?!
mysql> select id from ft_test where match (note_text)
against ('first');
Empty set (0.00 sec)
The word "fourth" = 1 row
mysql> select id from ft_test where match (note_text)
against ('fourth');
+----+
| id |
+----+
| 3 |
+----+
1 row in set (0.00 sec)
The word "another" = empty set?
mysql> select id from ft_test where match (note_text)
against ('another');
Empty set (0.00 sec)
Note that this is MySQL 3.23.49-nt, running on WinXP
Pro. I've only been using MySQL for a couple months
now and this is my first foray into the world of
FULLTEXT indexes. I found the problem on a much more
complex table query, but the above demonstrates the
issue in a more simple way. If you need more
information, or if there is some configuration option
I've overlooked, please let me know.
I appreciate any help I can get with this... while I
could probably use LOWER(note_text) LIKE '%<term>%'
for this purpose, it seems like this is what FULLTEXT
was made to do!
Thanks again,
-- jeff
__________________________________________________
Yahoo! - We Remember
9-11: A tribute to the more than 3,000 lives lost
http://dir.remember.yahoo.com/tribute
---------------------------------------------------------------------
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
