Jefferey,
Tuesday, September 10, 2002, 8:19:35 AM, you wrote:
JD> I'm just getting started with FULLTEXT indexes for a
JD> table and am seeing some very off-base results. While
JD> it may be something I'm doing, I didn't see anything
JD> in the online manual to indicate why these results
JD> would be so bizarre.
JD> Here is a test table I've created:
JD> create table ft_test
JD> (id smallint unsigned not null auto_increment,
JD> note_text text not null,
JD> fulltext (note_text),
JD> primary key (id));
JD> Once this table is created, I insert a few rows to
JD> test with:
JD> insert into ft_test values
JD> (null, 'this is the first row');
JD> insert into ft_test values
JD> (null, 'this is the second row with more text');
JD> insert into ft_test values
JD> (null, 'this is a fourth row with different text');
JD> insert into ft_test values
JD> (null, 'this is a fifth row for not another
JD> variation');
JD> insert into ft_test values
JD> (null, 'and here is a sixth row that should be
JD> sufficient');
JD> Ok, so far so good (yea, I know I forgot a "third"
JD> row)... but here's where it gets weird. All of the
JD> words I search on below appear in only ONE of the
JD> above rows and all of them are four or more
JD> characters. Despite this, some of them return a
JD> correct result and other rows, which clearly have a
JD> matching word, return an empty set.
JD> The word "different" = empty set?!
JD> mysql> select id from ft_test where match (note_text)
JD> against ('different');
JD> Empty set (0.00 sec)
JD> The word "sufficient" = 1 row
JD> mysql> select id from ft_test where match (note_text)
JD> against ('sufficient');
JD> +----+
JD> | id |
JD> +----+
JD> | 5 |
JD> +----+
JD> 1 row in set (0.00 sec)
JD> The word "first" = empty set?!
JD> mysql> select id from ft_test where match (note_text)
JD> against ('first');
JD> Empty set (0.00 sec)
JD> The word "fourth" = 1 row
JD> mysql> select id from ft_test where match (note_text)
JD> against ('fourth');
JD> +----+
JD> | id |
JD> +----+
JD> | 3 |
JD> +----+
JD> 1 row in set (0.00 sec)
JD> The word "another" = empty set?
JD> mysql> select id from ft_test where match (note_text)
JD> against ('another');
JD> Empty set (0.00 sec)
JD> Note that this is MySQL 3.23.49-nt, running on WinXP
JD> Pro. I've only been using MySQL for a couple months
JD> now and this is my first foray into the world of
JD> FULLTEXT indexes. I found the problem on a much more
JD> complex table query, but the above demonstrates the
JD> issue in a more simple way. If you need more
JD> information, or if there is some configuration option
JD> I've overlooked, please let me know.
JD> I appreciate any help I can get with this... while I
JD> could probably use LOWER(note_text) LIKE '%<term>%'
JD> for this purpose, it seems like this is what FULLTEXT
JD> was made to do!
Jefferey, "another", "first", "different" are stop words. You can find
list of stop words in the ft_static.c file in the source distribution.
--
For technical support contracts, goto https://order.mysql.com/?ref=ensita
This email is sponsored by Ensita.net http://www.ensita.net/
__ ___ ___ ____ __
/ |/ /_ __/ __/ __ \/ / Egor Egorov
/ /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED]
/_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net
<___/ www.mysql.com
---------------------------------------------------------------------
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
