fulltext searching

i can't seem to get the fulltext searching working perfectly... single search terms it works fine, multiple search terms it also seems to be ok - finding records with any of the search terms.
however, if i try the boolean mode it gives out garbage all the time. i try for example:
select * from mytable where match(file_text) against('+findthis +andthis -notthis' in boolean mode);

various variants of this never find the records, or find ALL the records in the database... when such filters should throw out a couple of records at least.

i'm running 4.0.1-alpha on freebsd... and i've set up the tables for fulltext indexes.

is this still heavy work-in-progress or should it work? does it work for anyone?

thanks,

marko

------------------------------------------------------------ ---------
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 bugs-thread12154@lists.mysql.com
To unsubscribe, e-mail

Re: fulltext searching

Hi!

On Jul 01, Marko Djukic wrote:
> i can't seem to get the fulltext searching working perfectly... single
> search terms it works fine, multiple search terms it also seems to be
> ok - finding records with any of the search terms. however, if i try
> the boolean mode it gives out garbage all the time. i try for example:
> select * from mytable where match(file_text) against('+findthis
> +andthis -notthis' in boolean mode);
>
> various variants of this never find the records, or find ALL the
> records in the database... when such filters should throw out a couple
> of records at least.
>
> i'm running 4.0.1-alpha on freebsd... and i've set up the tables for
> fulltext indexes.
>
> is this still heavy work-in-progress or should it work? does it work
> for anyone?

It is supposed to work :)

See mysql-test/t/fulltext.test file to see that it does work.

So, you probably, found a bug.
Would you mind creating a repeatable test case ?

Regards,
Sergei

--
MySQL Development Team
__ ___ ___ ____ __
/ |/ /_ __/ __/ __ \/ / Sergei Golubchik
/ /|_/ / // /\ \/ /_/ / /__ MySQL AB, http://www.mysql.com/
/_/ /_/\_, /___/\___\_\___/ Osnabrueck, Germany
<___/

------------------------------------------------------------ ---------
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 bugs-thread12156@lists.mysql.com
To unsubscribe, e-mail

Re: fulltext searching

Hi!

On Jul 01, Marko Djukic wrote:
> i can't seem to get the fulltext searching working perfectly... single
> search terms it works fine, multiple search terms it also seems to be
> ok - finding records with any of the search terms. however, if i try
> the boolean mode it gives out garbage all the time. i try for example:
> select * from mytable where match(file_text) against('+findthis
> +andthis -notthis' in boolean mode);
>
> various variants of this never find the records, or find ALL the
> records in the database... when such filters should throw out a couple
> of records at least.
>
> i'm running 4.0.1-alpha on freebsd... and i've set up the tables for
> fulltext indexes.
>
> is this still heavy work-in-progress or should it work? does it work
> for anyone?

It is supposed to work :)

See mysql-test/t/fulltext.test file to see that it does work.

So, you probably, found a bug.
Would you mind creating a repeatable test case ?

Regards,
Sergei

--
MySQL Development Team
__ ___ ___ ____ __
/ |/ /_ __/ __/ __ \/ / Sergei Golubchik
/ /|_/ / // /\ \/ /_/ / /__ MySQL AB, http://www.mysql.com/
/_/ /_/\_, /___/\___\_\___/ Osnabrueck, Germany
<___/

------------------------------------------------------------ ---------
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
To unsubscribe, e-mail
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Re: fulltext searching

Is there a tutorial or step-by-step introduction to fulltext searches with
MySQL somewhere?

Regards,

Eivind :-o





------------------------------------------------------------ ---------
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
To unsubscribe, e-mail
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Re: fulltext searching

section 6.8 in the manual i think...

Quoting "Eivind A. Sivertsen" :

> Is there a tutorial or step-by-step introduction to fulltext searches with
> MySQL somewhere?
>
> Regards,
>
> Eivind :-o
>
>
>
>




------------------------------------------------------------ ---------
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
To unsubscribe, e-mail
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

RE: fulltext searching

Hi

If anybody from mysql is listening / watching, www.mysql.com is down, and
has been for a while.

Peter



-----------------------------------------------
Excellence in internet and open source software
-----------------------------------------------
Sunmaia
www.sunmaia.net
info@sunmaia.net
tel. 0121-242-1473
-----------------------------------------------


------------------------------------------------------------ ---------
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
To unsubscribe, e-mail
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Re: fulltext searching

> section 6.8 in the manual i think...

Hmm, no; that's about the access privilege system...
I found it in section 24.2, but I feel this topic has gotten a bit scarce
mention in the manual.
Perhaps it deserves more than that...? I sure would appreciate something
extra <:-)


Best regards,

Eivind







------------------------------------------------------------ ---------
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
To unsubscribe, e-mail
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Re: Re: fulltext searching

Eivind,
Tuesday, July 02, 2002, 12:22:33 PM, you wrote:

EAS> Is there a tutorial or step-by-step introduction to fulltext searches with
EAS> MySQL somewhere?

Sure. In the MySQL manual:
http://www.mysql.com/doc/F/u/Fulltext_Search.html





--
For technical support contracts, goto https://order.mysql.com/?ref=ensita
This email is sponsored by Ensita.net http://www.ensita.net/
__ ___ ___ ____ __
/ |/ /_ __/ __/ __ \/ / Egor Egorov
/ /|_/ / // /\ \/ /_/ / /__ Egor.Egorov@ensita.net
/_/ /_/\_, /___/\___\_\___/ 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
To unsubscribe, e-mail
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Re: fulltext searching

there's not a huge lot more to cover than what is in the manual... what exactly are you looking for?

m.

Quoting "Eivind A. Sivertsen" :

> > section 6.8 in the manual i think...
>
> Hmm, no; that's about the access privilege system...
> I found it in section 24.2, but I feel this topic has gotten a bit scarce
> mention in the manual.
> Perhaps it deserves more than that...? I sure would appreciate something
> extra <:-)
>
>
> Best regards,
>
> Eivind
>
>
>
>
>
>




------------------------------------------------------------ ---------
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
To unsubscribe, e-mail
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Re: fulltext searching

plus are you sure you're reading the latest manual? it is chapter 6.8.

try this (google cache of the mysql.com/doc since mysql.com seems to be down):

http://www.google.com/search?q=cache:yvsB86pbs5cC:www.mysql. com/doc/F/u/Fulltext_Search.html+full+text+mysql&hl=en&ie=UT F-8

ciao,

m.

Quoting "Eivind A. Sivertsen" :

> > section 6.8 in the manual i think...
>
> Hmm, no; that's about the access privilege system...
> I found it in section 24.2, but I feel this topic has gotten a bit scarce
> mention in the manual.
> Perhaps it deserves more than that...? I sure would appreciate something
> extra <:-)
>
>
> Best regards,
>
> Eivind
>
>
>
>
>
>




------------------------------------------------------------ ---------
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
To unsubscribe, e-mail
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Re: fulltext searching

> plus are you sure you're reading the latest manual? it is chapter 6.8.


The location was correct for the onlien manual. I first tried my stored PDf
version, since I could not reach http://www.mysql.com
There, it was section 24.2. Now I printed then online section 6.8 and will
go through it.
My intention is to find out whether it would be feasible to try and build
full-text search on my website.

Thanks for your help!

Eivind :-)





------------------------------------------------------------ ---------
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
To unsubscribe, e-mail
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Re: fulltext searching

Hi!

Here are instructions on how to reproduce a bug I found using "fulltext
search" on MySQL Alpha-4.0.1 (Windows 2000). I found this message
thread when looking for an answer.

I hope it helps,

Marcos

QUICK BUG REPORT:
-----------------

How-To-Repeat:

Just run "mysql test < bug-file.txt" and you will notice that the
second SELECT statement returns an empty set, when it shouldn't, just
like the first one (by the way, I don't understant the "body" string
that appears during the program execution):

-- begin bug-file.txt --

create table articles
(
body varchar(200),
fulltext (body)
);

insert into articles values ('DBMS stands for DataBase Management ...');

select * from articles where match (body) against ('database');

insert into articles values ('After you went through a ...');

select * from articles where match (body) against ('database');

drop table articles;

-- end bug-file.txt --

DETAILED BUG REPORT:
--------------------

I'm using the following configuration:

MySQL version: 4.0.1-alpha-max-nt
Machine: Compaq Armada M700
OS: Microsoft Windows 2000 [Version 5.00.2195]
Memory: 128 MB

Dump of variables using "mysql -e "show variables" > variables.txt":

Variable_name Value
back_log 50
basedir C:\\mysql\\
bdb_cache_size 8388600
bdb_log_buffer_size 32768
bdb_home C:\\mysql\\data\\
bdb_max_lock 10000
bdb_logdir
bdb_shared_data OFF
bdb_tmpdir C:\\WINNT\\TEMP\\
bdb_version Sleepycat Software: Berkeley DB 3.2.9a: (December 23, 2001)
binlog_cache_size 32768
character_set latin1
character_sets latin1 big5 czech euc_kr gb2312 gbk latin1_de sjis tis620
ujis dec8 dos german1 hp8 koi8_ru latin2 swe7 usa7 cp1251 danish hebrew
win1251 estonia hungarian koi8_ukr win1251ukr greek win1250 croat cp1257
latin5
concurrent_insert ON
connect_timeout 5
datadir C:\\mysql\\data\\
delay_key_write ON
delayed_insert_limit 100
delayed_insert_timeout 300
delayed_queue_size 1000
flush OFF
flush_time 1800
ft_min_word_len 4
ft_max_word_len 254
ft_max_word_len_for_sort 20
ft_boolean_syntax + -><()~*:""&|
have_bdb YES
have_innodb YES
have_isam NO
have_raid NO
have_symlink YES
have_openssl NO
init_file
innodb_additional_mem_pool_size 1048576
innodb_buffer_pool_size 8388608
innodb_data_file_path ibdata1:64M
innodb_data_home_dir
innodb_file_io_threads 9
innodb_force_recovery 0
innodb_thread_concurrency 8
innodb_flush_log_at_trx_commit OFF
innodb_fast_shutdown OFF
innodb_flush_method
innodb_lock_wait_timeout 1073741824
innodb_log_arch_dir
innodb_log_archive OFF
innodb_log_buffer_size 1048576
innodb_log_file_size 5242880
innodb_log_files_in_group 2
innodb_log_group_home_dir o,C
innodb_mirrored_log_groups 1
interactive_timeout 28800
join_buffer_size 131072
key_buffer_size 8388600
language C:\\mysql\\share\\english\\
large_files_support ON
log OFF
log_update OFF
log_bin OFF
log_slave_updates OFF
log_long_queries OFF
long_query_time 10
low_priority_updates OFF
lower_case_table_names 1
max_allowed_packet 1048576
max_binlog_cache_size 4294967295
max_binlog_size 1073741824
max_connections 100
max_connect_errors 10
max_delayed_threads 20
max_heap_table_size 16777216
max_join_size 4294967295
max_sort_length 1024
max_user_connections 0
max_tmp_tables 32
max_write_lock_count 4294967295
myisam_bulk_insert_tree_size 8388608
myisam_max_extra_sort_file_size 256
myisam_max_sort_file_size 2047
myisam_recover_options OFF
myisam_sort_buffer_size 8388608
net_buffer_length 16384
net_read_timeout 30
net_retry_count 10
net_write_timeout 60
open_files_limit 0
pid_file C:\\mysql\\data\\AC3J02DM74W4EW.pid
port 3306
protocol_version 10
record_buffer 131072
record_rnd_buffer 131072
rpl_recovery_rank 0
query_buffer_size 0
query_cache_limit 1048576
query_cache_size 0
query_cache_startup_type 1
safe_show_database OFF
server_id 0
slave_net_timeout 3600
skip_locking ON
skip_networking OFF
skip_show_database OFF
slow_launch_time 2
socket MySQL
sort_buffer 2097144
sql_mode 0
table_cache 64
table_type MYISAM
thread_cache_size 0
thread_stack 65536
transaction_isolation READ-COMMITTED
timezone E. South America Standard Time
tmp_table_size 33554432
tmpdir C:\\WINNT\\TEMP\\
version 4.0.1-alpha-max-nt
wait_timeout 28800

-- end of bug report --

____________________________________________________________ _____
Send and receive Hotmail on your mobile device: http://mobile.msn.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 bugs-thread12206@lists.mysql.com
To unsubscribe, e-mail

Re: fulltext searching

Hi!

On Jul 21, Marcos Chaves wrote:
> Hi!
>
> Here are instructions on how to reproduce a bug I found using "fulltext
> search" on MySQL Alpha-4.0.1 (Windows 2000). I found this message
> thread when looking for an answer.
>
> I hope it helps,
>
> Marcos
>
> QUICK BUG REPORT:
> -----------------
>
> How-To-Repeat:
>
> Just run "mysql test < bug-file.txt" and you will notice that the
> second SELECT statement returns an empty set, when it shouldn't, just
> like the first one
>
> -- begin bug-file.txt --
> create table articles ( body varchar(200), fulltext (body) );
> insert into articles values ('DBMS stands for DataBase Management ...');
> select * from articles where match (body) against ('database');
> insert into articles values ('After you went through a ...');
> select * from articles where match (body) against ('database');
> drop table articles;
> -- end bug-file.txt --

It's not a bug, but rather a feature. And it is documented in the
manual: http://www.mysql.com/doc/F/u/Fulltext_Search.html

===
Such a technique works best with large collections (in fact, it was
carefully tuned this way). For very small tables, word distribution
does not reflect adequately their semantic value, and this model may
sometimes produce bizarre results.

mysql> SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('MySQL');
Empty set (0.00 sec)

The search for the word MySQL produces no results in the above example,
because that word is present in more than half the rows. As such, it is
effectively treated as a stopword (that is, a word with zero semantic
value). This is the most desirable behaviour -- a natural language
query should not return every second row from a 1GB table.
===

Regards,
Sergei

--
MySQL Development Team
__ ___ ___ ____ __
/ |/ /_ __/ __/ __ \/ / Sergei Golubchik
/ /|_/ / // /\ \/ /_/ / /__ MySQL AB, http://www.mysql.com/
/_/ /_/\_, /___/\___\_\___/ Osnabrueck, Germany
<___/

------------------------------------------------------------ ---------
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 bugs-thread12208@lists.mysql.com
To unsubscribe, e-mail

Re: fulltext searching / BUG report

Sergei,

finally managed to try out this solution, resolved my out of disk space problem...

and it works now! just as you found out the boolean searches work fine now...

any idea what causes the corruption in the first place? different charsets?
because this happens every time i load up the database from zero. it's not a
horrible thing, but still a bit weird having to tell customers that they need to repair
the database each time they load it up.

plus would like to somehow find out if it stays fixed or eventually gets corrupted
again? any ideas?

thanks!

marko


Quoting Sergei Golubchik :

> Hi!
>
> Thanks, I got your CvFiles.MYI file.
>
> It has charset latin1.
>
> After I recreated the index with 'repair table' I was able to repeat the
> bug. But still when I tried to do "hard repair" with
>
> REPAIR TABLE CvFiles USE_FRM;
>
> the bug dissapeared. I tried different charsets, but the bug never came
> uip again. Looks like your myi file is either old or has some subtle
> corruption. Try the above "hard repair" command.
>
> Regards,
> Sergei
>
> --
> MySQL Development Team
> __ ___ ___ ____ __
> / |/ /_ __/ __/ __ \/ / Sergei Golubchik
> / /|_/ / // /\ \/ /_/ / /__ MySQL AB, http://www.mysql.com/
> /_/ /_/\_, /___/\___\_\___/ Osnabrueck, Germany
> <___/
>




------------------------------------------------------------ ---------
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
To unsubscribe, e-mail
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Re: fulltext searching / BUG report

sergei,

no i meant loading it up from raw data again. in the sense, i empty the
database, then i have a script which takes a directory full of files and reads
them into the database. each time i do that the database ends up corrupted.

is there any way to figure out where the corruption is? is it the files?
different character sets (some are italian, some english, etc)? or maybe
something that my script is doing?


thanks,

marko


Quoting Sergei Golubchik :

> Hi!
>
> On Jul 26, Marko Djukic wrote:
> > Sergei,
> >
> > finally managed to try out this solution, resolved my out of disk
> > space problem...
> >
> > and it works now! just as you found out the boolean searches work fine
> > now...
> >
> > any idea what causes the corruption in the first place? different
> > charsets? because this happens every time i load up the database from
> > zero. it's not a horrible thing, but still a bit weird having to tell
> > customers that they need to repair the database each time they load it
> > up.
>
> Strange.
> I dumped the whole table with mysqldump, and loaded it up again.
> No bug - works fine for me.
>
> Regards,
> Sergei
>
> --
> MySQL Development Team
> __ ___ ___ ____ __
> / |/ /_ __/ __/ __ \/ / Sergei Golubchik
> / /|_/ / // /\ \/ /_/ / /__ MySQL AB, http://www.mysql.com/
> /_/ /_/\_, /___/\___\_\___/ Osnabrueck, Germany
> <___/
>




------------------------------------------------------------ ---------
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
To unsubscribe, e-mail
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php