fulltext searching using special chars

[Octavian Rasnita]

Hi,

I am trying to search for a word that contains special chars like ş
or ţ, but I find only the words  and  like when the special
chars are not a part of the word.

I found that if I search for "ş" (in boolean mode), I am able to
find the records that contain the full word, but I would like to be able to
do this without needing to use quotes.

If it is possible, pleas tell me how.

Thank you.

Teddy



-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Strange issue with fulltext searching (is "self" reserved)?

[Michael Stassen]

It isn't a reserved word, but "self" is a full-text stopword, so it isn't 
indexed.

If you've built from source, the stopwords are in
  path-to-source/myisam/ft_static.c
You can create your own stopword list, or turn off stopwords altogether, if 
you want.  See  
for the details.

Michael
Jeremy Durham wrote:
I have a problem with searching for a specific term. I have a database 
of about 50k names, and am attempting to search the lastname field for 
'self'. My query looks something like this:

SELECT * FROM users where MATCH (first_name, last_name) against ('self').
This query works fine if I use any other word except "self". For 
example, if I use "smith" or "johnson" I get the desired results, but 
the name self does not return any results, even though I have verified 
there are 6-7 people with the last name self in the database. Strangely, 
changing their last name to self1 or sel or anything else and then 
searching for the new term results in me locating the record. Upon 
changing the name back and rerunning the query I am unable to find them.

Is "self" a reserved word? I looked and did not find it.
FWIW I am running MySQL 4.0.18 on Debian.
Jeremy
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Strange issue with fulltext searching (is "self" reserved)?

[Jeremy Durham]

I have a problem with searching for a specific term. I have a database 
of about 50k names, and am attempting to search the lastname field for 
'self'. My query looks something like this:

SELECT * FROM users where MATCH (first_name, last_name) against ('self').
This query works fine if I use any other word except "self". For 
example, if I use "smith" or "johnson" I get the desired results, but 
the name self does not return any results, even though I have verified 
there are 6-7 people with the last name self in the database. Strangely, 
changing their last name to self1 or sel or anything else and then 
searching for the new term results in me locating the record. Upon 
changing the name back and rerunning the query I am unable to find them.

Is "self" a reserved word? I looked and did not find it.
FWIW I am running MySQL 4.0.18 on Debian.
Jeremy
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

RE: Fulltext searching

[emierzwa]

I beleive this is the built-in word list file you were looking for. And the
word "beyond" was in the list. It would probably be helpful if there were
a SHOW command that listed the active built-in stopwords.

Ed

-
#include "ftdefs.h"
ulong ft_min_word_len=4;
ulong ft_max_word_len=HA_FT_MAXCHARLEN;
ulong ft_query_expansion_limit=5;
char ft_boolean_syntax[]="+ -><()~*:\"\"&|";

const HA_KEYSEG ft_keysegs[FT_SEGS]={
{
HA_KEYTYPE_VARTEXT,   /* type */
63,   /* language (will be overwritten) */
0, 0, 0,  /* null_bit, bit_start, bit_end */
HA_VAR_LENGTH | HA_PACK_KEY,  /* flag */
HA_FT_MAXBYTELEN, /* length */
HA_FT_WLEN,   /* start */
0,/* null_pos */
NULL  /* charset  */
  },
  {
/*
  Note, this (and the last HA_KEYTYPE_END) segment should NOT
  be packed in any way, otherwise w_search() won't be able to
  update key entry 'in vivo'
*/
  HA_FT_WTYPE, 63, 0, 0, 0, HA_NO_SORT, HA_FT_WLEN, 0, 0, NULL
  }
};

const struct _ft_vft _ft_vft_nlq = {
  ft_nlq_read_next, ft_nlq_find_relevance, ft_nlq_close_search,
  ft_nlq_get_relevance,  ft_nlq_reinit_search
};
const struct _ft_vft _ft_vft_boolean = {
  ft_boolean_read_next, ft_boolean_find_relevance, ft_boolean_close_search,
  ft_boolean_get_relevance,  ft_boolean_reinit_search
};


FT_INFO *ft_init_search(uint flags, void *info, uint keynr,
byte *query, uint query_len, byte *record)
{
  FT_INFO *res;
  if (flags & FT_BOOL)
res= ft_init_boolean_search((MI_INFO *)info, keynr, query, query_len);
  else
res= ft_init_nlq_search((MI_INFO *)info, keynr, query, query_len, flags,
record);
  return res;
}

const char *ft_stopword_file = 0;
const char *ft_precompiled_stopwords[] = {

#ifdef COMPILE_STOPWORDS_IN

/* This particular stopword list was taken from SMART distribution
   ftp://ftp.cs.cornell.edu/pub/smart/smart.11.0.tar.Z
   it was slightly modified to my taste, though
 */

  "a's",
  "able",
  "about",
  "above",
  "according",
  "accordingly",
  "across",
  "actually",
  "after",
  "afterwards",
  "again",
  "against",
  "ain't",
  "all",
  "allow",
  "allows",
  "almost",
  "alone",
  "along",
  "already",
  "also",
  "although",
  "always",
  "am",
  "among",
  "amongst",
  "an",
  "and",
  "another",
  "any",
  "anybody",
  "anyhow",
  "anyone",
  "anything",
  "anyway",
  "anyways",
  "anywhere",
  "apart",
  "appear",
  "appreciate",
  "appropriate",
  "are",
  "aren't",
  "around",
  "as",
  "aside",
  "ask",
  "asking",
  "associated",
  "at",
  "available",
  "away",
  "awfully",
  "be",
  "became",
  "because",
  "become",
  "becomes",
  "becoming",
  "been",
  "before",
  "beforehand",
  "behind",
  "being",
  "believe",
  "below",
  "beside",
  "besides",
  "best",
  "better",
  "between",
  "beyond",
  "both",
  "brief",
  "but",
  "by",
  "c'mon",
  "c's",
  "came",
  "can",
  "can't",
  "cannot",
  "cant",
  "cause",
  "causes",
  "certain",
  "certainly",
  "changes",
  "clearly",
  "co",
  "com",
  "come",
  "comes",
  "concerning",
  "consequently",
  "consider",
  "considering",
  "contain",
  "containing",
  "contains",
  "corresponding",
  "could",
  "couldn't",
  "course",
  "currently",
  "definitely",
  "described",
  "despite",
  "did",
  "didn't",
  "different",
  "do",
  "does",
  "doesn't",
  "doing",
  "don't",
  "done",
  "down",
  "downwards",
  "during",
  "each",
  "edu",
  "eg",
  "eight",
  "either",
  "else",
  "elsewhere",
  "enough",
  "entirely",
  "especially",
  "et",
  "etc",
  "even",
  "ever",
  "every",
  "everybody",
  "everyone",
  "everything",
  "everywhere",
  "ex",
  "exactly",
  "example",
  "except",
  "far",
  "few",
  "fifth",
  "first",
  "five",
  "followed",
  "following",
  "follows",
  "for",
  "former",
  "formerly",
  "forth",
  "four",
  "from",
  "further",
  "furthermore",
  "get",
  "gets",
  "getting",
  "given",
  "gives",
  "go",
  "goes",
  "going",
  "gone",
  "got",
  "gotten",
  "greetings",
  "had",
  "hadn't",
  "happens",
  "hardly",
  "has",
  "hasn't",
  "have",
  "haven't",
  "having",
  "he",
  "he's",
  "hello",
  "help",
  "hence",
  "her",
  "here",
  "here's",
  "hereafter",
  "hereby",
  "herein",
  "hereupon",
  "hers",
  "herself",
  "hi",
  "him",
  "himself",
  "his",
  "hither",
  "hopefully",
  "how",
  "howbeit",
  "however",
  "i'd",
  "i'll",
  "i'm",
  "i've",
  "ie",
  "if",
  "ignored",
  "immediate",
  "in",
  "inasmuch",
  "inc",
  "indeed",
  "indicate",
  "indicated",
  "indicates",
  "inner",
  "insofar",
  "instead",
  "into",
  "inward",
  "is",
  "isn't",
  "it",
  "it'd",
  "it'll",
  "it's",
  "its",
  "itself",
  "just",
  "keep",
  "keeps",
  "kept",
  "know",
  "knows",
  "known",
  "last",
  "lately",
  "later",
  "latter",
  "latterly",
  "least",
  "less",
  "lest",
  "let

Re: Fulltext searching

[Richard Baskett]

on 5/6/04 16:53, Michael Stassen at [EMAIL PROTECTED] wrote:

> If you've built from source, the stopwords are in
> 
>  path-to-source/myisam/ft_static.c
> 
> In my copy of 4.0.18, beyond is in the list.
> 
> You can create your own stopword list, or turn off stopwords altogether, if
> you want.  See 
> for the details.

I just turned it off and it looks like it's working again.  I couldn¹t find
the source.. so my guess is that it's a binary... and I guess built in?
Once I set the variable in the my.cnf file to '' and reindexed the fulltext
fields, everything looks to be working the way I needed it to work!

So thank you for sending me in the right direction.. you have saved me a LOT
of time! :)

Cheers!

Rick


"Work like you don't need the money. Dance like no one is watching. And love
like you've never been hurt." - Mark Twain


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Fulltext searching

[Michael Stassen]

If you've built from source, the stopwords are in
  path-to-source/myisam/ft_static.c
In my copy of 4.0.18, beyond is in the list.
You can create your own stopword list, or turn off stopwords altogether, if 
you want.  See <http://dev.mysql.com/doc/mysql/en/Fulltext_Fine-tuning.html> 
for the details.

Michael
Richard Baskett wrote:
In my system variables it shown that the stopword file is "(built-in)" so
where would I find it then if it is built in..  'Beyond" does not seem like
a word that would be in a built-in stopword file does it?  Could it be
reserved?
Thanks!
Rick  

on 5/6/04 15:30, Dathan Vance Pattishall at [EMAIL PROTECTED] wrote:

Check to see if beyond is in your stopword file.

-Original Message-
From: Richard Baskett [mailto:[EMAIL PROTECTED]
Sent: Thursday, May 06, 2004 2:12 PM
To: MySQL
Subject: Fulltext searching
Ok having some problems with MySQL's fulltext search.  I have the fields
that I need fulltext indexed, everything seems to be working correctly,
but
for some reason when I search for "beyond" looking for an item called:
"Beyond Heaven yoga & Day Spa"
It doesn¹t find it..  I am searching in boolean mode by the way.  but if I
search for "heaven" it does find it.  I have no idea why it will not find
that first word...  Actually it finds it when using all the words, except
when I use "beyond"
Now it just occurred to me.. if there is only one result.. will it not
show
that there was a result?  Or is it something completely different?
Thanks!
Rick
"What lies behind us and what lies before us are tiny matters compared to
what lies within us." - Oliver Wendell Holmes



--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Fulltext searching

[Richard Baskett]

In my system variables it shown that the stopword file is "(built-in)" so
where would I find it then if it is built in..  'Beyond" does not seem like
a word that would be in a built-in stopword file does it?  Could it be
reserved?

Thanks!

Rick  

on 5/6/04 15:30, Dathan Vance Pattishall at [EMAIL PROTECTED] wrote:

> Check to see if beyond is in your stopword file.
> 
>> -Original Message-
>> From: Richard Baskett [mailto:[EMAIL PROTECTED]
>> Sent: Thursday, May 06, 2004 2:12 PM
>> To: MySQL
>> Subject: Fulltext searching
>> 
>> Ok having some problems with MySQL's fulltext search.  I have the fields
>> that I need fulltext indexed, everything seems to be working correctly,
>> but
>> for some reason when I search for "beyond" looking for an item called:
>> 
>> "Beyond Heaven yoga & Day Spa"
>> 
>> It doesn¹t find it..  I am searching in boolean mode by the way.  but if I
>> search for "heaven" it does find it.  I have no idea why it will not find
>> that first word...  Actually it finds it when using all the words, except
>> when I use "beyond"
>> 
>> Now it just occurred to me.. if there is only one result.. will it not
>> show
>> that there was a result?  Or is it something completely different?
>> 
>> Thanks!
>> 
>> Rick
>> 
>> "What lies behind us and what lies before us are tiny matters compared to
>> what lies within us." - Oliver Wendell Holmes


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

RE: Fulltext searching

[Dathan Vance Pattishall]

Check to see if beyond is in your stopword file.

> -Original Message-
> From: Richard Baskett [mailto:[EMAIL PROTECTED]
> Sent: Thursday, May 06, 2004 2:12 PM
> To: MySQL
> Subject: Fulltext searching
> 
> Ok having some problems with MySQL's fulltext search.  I have the fields
> that I need fulltext indexed, everything seems to be working correctly,
> but
> for some reason when I search for "beyond" looking for an item called:
> 
> "Beyond Heaven yoga & Day Spa"
> 
> It doesn¹t find it..  I am searching in boolean mode by the way.  but if I
> search for "heaven" it does find it.  I have no idea why it will not find
> that first word...  Actually it finds it when using all the words, except
> when I use "beyond"
> 
> Now it just occurred to me.. if there is only one result.. will it not
> show
> that there was a result?  Or is it something completely different?
> 
> Thanks!
> 
> Rick
> 
> "What lies behind us and what lies before us are tiny matters compared to
> what lies within us." - Oliver Wendell Holmes
> 
> 
> 
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:
> http://lists.mysql.com/[EMAIL PROTECTED]




--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Fulltext searching

[Richard Baskett]

Ok having some problems with MySQL's fulltext search.  I have the fields
that I need fulltext indexed, everything seems to be working correctly, but
for some reason when I search for "beyond" looking for an item called:

"Beyond Heaven yoga & Day Spa"

It doesn¹t find it..  I am searching in boolean mode by the way.  but if I
search for "heaven" it does find it.  I have no idea why it will not find
that first word...  Actually it finds it when using all the words, except
when I use "beyond"

Now it just occurred to me.. if there is only one result.. will it not show
that there was a result?  Or is it something completely different?

Thanks!

Rick

"What lies behind us and what lies before us are tiny matters compared to
what lies within us." - Oliver Wendell Holmes



--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Bug in Boolean mode fulltext searching.

[Michael Stassen]

Joe Rhett wrote:

mysql> select Notice_ID from Notices where match (Text) against 
('+pollution +control' in boolean mode);
Empty set (0.00 sec)

mysql> select Notice_ID from Notices where match (Text) against 
('"pollution control"' in boolean mode);
Empty set (0.02 sec)

mysql> select Notice_ID from Notices where Text like '%pollution 
control%';
+---+
| Notice_ID |
+---+
|192090 |
+---+
1 row in set (5.00 sec)
Your LIKE query is not equivalent to your MATCH AGAINST.  For example, 
Text containing "pollution controls" would match LIKE '%pollution 
control%', but would not MATCH AGAINST ('+pollution +control'...).  Have 
you looked at Text for this row to be sure it contains exactly 
"pollution" and "control"?
No, it isn't equivalent but it would match less than the AND would,
since it is an exact phrase.  Yes, those words and that exact phrase 
exists multiple times.  It has been confirmed as a bug.

mysql> select Notice_ID from Notices where match (Text) against 
('+pollution +air' in boolean mode);
Empty set (0.03 sec)

mysql> select Notice_ID from Notices where match (Text) against ('"air 
pollution"' in boolean mode);
Empty set (0.00 sec)

mysql> select Notice_ID from Notices where Text like '%air pollution%';
+---+
| Notice_ID |
+---+
|196349 |
|196569 |
|188183 |
|192090 |
|192686 |
|199283 |
+---+
6 rows in set (0.17 sec)
(NOTE on the search for air -- my.cnf has ft_min_word_len=3)
Are you certain that air is indexed?  What does

 SELECT COUNT(*) FROM Notices WHERE MATCH (Text) AGAINST ('air');

return?


Just over 6 thousand results.  And it was answered just below, here:


All OR searches work perfectly fine, as per:

mysql> select count(*) from Notices where match (Text) against ('air 
pollution');
+--+
| count(*) |
+--+
|  100 |
+--+
1 row in set (0.03 sec)
How many do you get with

 SELECT COUNT(*) FROM Notices WHERE MATCH (Text) AGAINST ('pollution');

If air isn't indexed, I'd expect 0 hits for 'air' and 100 hits for 
'pollution'.
 
17 hits for pollution.

Do you normally talk down to people as if they are too retarted to have
tested the obvious themselves?  This wasn't a complaint or a how-to
question, but a researched and tested bug.  I specifically demonstrated
that the phrases exist and that clearly stated that the OR searches worked.
I'm sorry.  It was not my intention to talk down to you or otherwise 
give offense, but given your obvious frustration with this problem, I 
chose my words poorly.  I apologize.

I wrote my reply before Sergei recognized the problem and told you how 
to fix it (and even he asked for more info).  While you may have done 
days of research, we on the list only had the evidence you gave to go 
on.  It is important when troubleshooting not to make assumptions.  As 
you only provided query results, not actual data, I was hoping to elicit 
some additional info to help with the diagnosis.

For example, you gave a query using LIKE to demonstrate that there 
should be at least one match for your first MATCH AGAINST, but your LIKE 
test is not a subset of your MATCH AGAINST.  You are correct that LIKE 
will only return the subset of returns from MATCH AGAINST that are exact 
phrases, but it will also return things which MATCH AGAINST will not.  I 
gave one examaple, namely a phrase containing "pollution controls".  The 
's' at the end would satisfy the % of the LIKE test but 'controls' is 
not 'control' to MATCH AGAINST.  You may know that there are many rows 
which should have matched, but you didn't show us one.

In the case of 'air pollution', I must admit I failed to say what I was 
getting at.  There have been questions before where mysql was not using 
ft_min_word_len=3 (something as trivial as putting it in the wrong 
section in my.cnf, for example) as intended.  I thought it important to 
rule that out.  Knowing that OR searches work does not rule that out. 
Try it:

  SELECT COUNT(*) FROM Notices
  WHERE MATCH (Text) AGAINST ('no pollution');
should return the same 17 rows as the simple MATCH AGAINST ('pollution') 
did.  The word 'no' is too short, isn't indexed, and is thus ignored in 
your query.  If ft_min_word_len had not been successfully set to 3, 
'air' would have been ignored.  In this message you provided the fact 
that a MATCH AGAINST 'air' returns over 6 thousand hits, which clearly 
shows that ft_min_word_len is set to 3, as you intended.  Again, while 
you had probably already verified that air was indexed, you didn't show us.

I did not take your message to be "a complaint or a how-to question", 
but a real problem to be solved.  In rereading my message, I can see how 
someone, especially someone already frustrated with the problem, could 
take my questions as a suggestion that you didn't know what you were 
doing.  I didn't mean to do that, and I'm sorry I wrote it that way. 
But, to be fair, you must admit that for all your research, this w

Re: Bug in Boolean mode fulltext searching.

[Joe Rhett]

> > Based on a guess, or did you analyze the data file I sent?
> 
> Based on your data.
..  ..
> And of course I tried this myself before writing to you :)
  
Cool. Thanks for the analysis.

> > And if so, may I suggest that the upgrade documentation REALLY needs to
> > be broken into sections?
> 
> Yes, I agree. Not necesarily to these particular division, but to the
> fact that our "upgrading" sections are difficult to follow.
> I'm forwarding this request to our doc team.
  
I don't really care what divisions, just that it's easier to separate out
changes that need to be made to production databases during the upgrade
versus changes that need to be made to code using the new system.

-- 
Joe Rhett  Chief Geek
[EMAIL PROTECTED]  Isite Services, Inc.

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Bug in Boolean mode fulltext searching.

[Sergei Golubchik]

Hi!

On Jan 13, Joe Rhett wrote:
> > Not a bug.
> > In the manual, section "Upgrading from Version 3.23 to 4.0", there is
> > 
> >* To use `MATCH ... AGAINST (... IN BOOLEAN MODE)' with your tables,
> >  you need to rebuild them with `REPAIR TABLE table_name USE_FRM'.
>  
> Based on a guess, or did you analyze the data file I sent?

Based on your data.
There is a ft_dump utility program that comes from source distribution.

I did 

  ft_dump -d Notices 0|grep '\<\(control\|pollution\)\>' > log

then I noticed that entries are ordered by weight, not by rowid.
It is what was changed in 4.0 to make boolean search to work,
and it's what is fixed by `REPAIR TABLE table_name USE_FRM'

And of course I tried this myself before writing to you :)
 
> And if so, may I suggest that the upgrade documentation REALLY needs to
> be broken into sections?
> 
> 1. Table changes
> 2. Privilege changes
> 3. Configuration changes
> 4. API/result changes

Yes, I agree. Not necesarily to these particular division, but to the
fact that our "upgrading" sections are difficult to follow.
I'm forwarding this request to our doc team.
 
Regards,
Sergei

-- 
   __  ___ ___   __
  /  |/  /_ __/ __/ __ \/ /   Sergei Golubchik <[EMAIL PROTECTED]>
 / /|_/ / // /\ \/ /_/ / /__  MySQL AB, Senior Software Developer
/_/  /_/\_, /___/\___\_\___/  Osnabrueck, Germany
   <___/  www.mysql.com

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Bug in Boolean mode fulltext searching.

[Joe Rhett]

> Not a bug.
> In the manual, section "Upgrading from Version 3.23 to 4.0", there is
> 
>* To use `MATCH ... AGAINST (... IN BOOLEAN MODE)' with your tables,
>  you need to rebuild them with `REPAIR TABLE table_name USE_FRM'.
 
Based on a guess, or did you analyze the data file I sent?

And if so, may I suggest that the upgrade documentation REALLY needs to
be broken into sections?

1. Table changes
2. Privilege changes
3. Configuration changes
4. API/result changes

Yeah, I did overlook that statement -- stuck between a large number of
notes about result changes relative to character set implementations.

-- 
Joe Rhett  Chief Geek
[EMAIL PROTECTED]  Isite Services, Inc.

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Bug in Boolean mode fulltext searching.

[Joe Rhett]

> >mysql> select Notice_ID from Notices where match (Text) against 
> >('+pollution +control' in boolean mode);
> >Empty set (0.00 sec)
> >
> >mysql> select Notice_ID from Notices where match (Text) against 
> >('"pollution control"' in boolean mode);
> >Empty set (0.02 sec)
> >
> >mysql> select Notice_ID from Notices where Text like '%pollution 
> >control%';
> >+---+
> >| Notice_ID |
> >+---+
> >|192090 |
> >+---+
> >1 row in set (5.00 sec)
> 
> Your LIKE query is not equivalent to your MATCH AGAINST.  For example, 
> Text containing "pollution controls" would match LIKE '%pollution 
> control%', but would not MATCH AGAINST ('+pollution +control'...).  Have 
> you looked at Text for this row to be sure it contains exactly 
> "pollution" and "control"?

No, it isn't equivalent but it would match less than the AND would,
since it is an exact phrase.  Yes, those words and that exact phrase 
exists multiple times.  It has been confirmed as a bug.

> >mysql> select Notice_ID from Notices where match (Text) against 
> >('+pollution +air' in boolean mode);
> >Empty set (0.03 sec)
> >
> >mysql> select Notice_ID from Notices where match (Text) against ('"air 
> >pollution"' in boolean mode);
> >Empty set (0.00 sec)
> >
> >mysql> select Notice_ID from Notices where Text like '%air pollution%';
> >+---+
> >| Notice_ID |
> >+---+
> >|196349 |
> >|196569 |
> >|188183 |
> >|192090 |
> >|192686 |
> >|199283 |
> >+---+
> >6 rows in set (0.17 sec)
> >
> >(NOTE on the search for air -- my.cnf has ft_min_word_len=3)
> 
> Are you certain that air is indexed?  What does
> 
>   SELECT COUNT(*) FROM Notices WHERE MATCH (Text) AGAINST ('air');
> 
> return?

Just over 6 thousand results.  And it was answered just below, here:

> >All OR searches work perfectly fine, as per:
> >
> >mysql> select count(*) from Notices where match (Text) against ('air 
> >pollution');
> >+--+
> >| count(*) |
> >+--+
> >|  100 |
> >+--+
> >1 row in set (0.03 sec)
> 
> How many do you get with
> 
>   SELECT COUNT(*) FROM Notices WHERE MATCH (Text) AGAINST ('pollution');
> 
> If air isn't indexed, I'd expect 0 hits for 'air' and 100 hits for 
> 'pollution'.
 
17 hits for pollution.

Do you normally talk down to people as if they are too retarted to have
tested the obvious themselves?  This wasn't a complaint or a how-to
question, but a researched and tested bug.  I specifically demonstrated
that the phrases exist and that clearly stated that the OR searches worked.

-- 
Joe Rhett  Chief Geek
[EMAIL PROTECTED]  Isite Services, Inc.

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Bug in Boolean mode fulltext searching.

[Sergei Golubchik]

Hi!

On Jan 07, [EMAIL PROTECTED] wrote:
> >Description:
>   Boolean mode fulltext searching returns zero hits for valid queries. 
> >How-To-Repeat:
>   Create a database with a Text column.  Add a fulltext index on it.
>   Try to search for multiple words with AND or phrase syntax.
> 
>   Here are examples:
> 
> mysql> select Notice_ID from Notices where match (Text) against
> ('+pollution +control' in boolean mode);
> Empty set (0.00 sec)
> 
> mysql> select Notice_ID from Notices where match (Text) against
> ('"pollution control"' in boolean mode);
> Empty set (0.02 sec)
> 
> mysql> select Notice_ID from Notices where Text like '%pollution control%';  
>
> +---+
> | Notice_ID |
> +---+
> |192090 |
> +---+
> 1 row in set (5.00 sec)

Not a bug.
In the manual, section "Upgrading from Version 3.23 to 4.0", there is

   * To use `MATCH ... AGAINST (... IN BOOLEAN MODE)' with your tables,
 you need to rebuild them with `REPAIR TABLE table_name USE_FRM'.

Regards,
Sergei

-- 
   __  ___ ___   __
  /  |/  /_ __/ __/ __ \/ /   Sergei Golubchik <[EMAIL PROTECTED]>
 / /|_/ / // /\ \/ /_/ / /__  MySQL AB, Senior Software Developer
/_/  /_/\_, /___/\___\_\___/  Osnabrueck, Germany
   <___/  www.mysql.com

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Bug in Boolean mode fulltext searching.

[Michael Stassen]

[EMAIL PROTECTED] wrote:

Description:
	Boolean mode fulltext searching returns zero hits for valid queries. 

How-To-Repeat:
Create a database with a Text column.  Add a fulltext index on it.
Try to search for multiple words with AND or phrase syntax.
	Here are examples:

mysql> select Notice_ID from Notices where match (Text) against ('+pollution +control' 
in boolean mode);
Empty set (0.00 sec)
mysql> select Notice_ID from Notices where match (Text) against ('"pollution control"' 
in boolean mode);
Empty set (0.02 sec)
mysql> select Notice_ID from Notices where Text like '%pollution control%'; 
+---+
| Notice_ID |
+---+
|192090 |
+---+
1 row in set (5.00 sec)
Your LIKE query is not equivalent to your MATCH AGAINST.  For example, 
Text containing "pollution controls" would match LIKE '%pollution 
control%', but would not MATCH AGAINST ('+pollution +control'...).  Have 
you looked at Text for this row to be sure it contains exactly 
"pollution" and "control"?

mysql> select Notice_ID from Notices where match (Text) against ('+pollution +air' in 
boolean mode);
Empty set (0.03 sec)
mysql> select Notice_ID from Notices where match (Text) against ('"air pollution"' in 
boolean mode);
Empty set (0.00 sec)
mysql> select Notice_ID from Notices where Text like '%air pollution%';
+---+
| Notice_ID |
+---+
|196349 |
|196569 |
|188183 |
|192090 |
|192686 |
|199283 |
+---+
6 rows in set (0.17 sec)
(NOTE on the search for air -- my.cnf has ft_min_word_len=3)
Are you certain that air is indexed?  What does

  SELECT COUNT(*) FROM Notices WHERE MATCH (Text) AGAINST ('air');

return?

All OR searches work perfectly fine, as per:

mysql> select count(*) from Notices where match (Text) against ('air pollution');
+--+
| count(*) |
+--+
|  100 |
+--+
1 row in set (0.03 sec)
How many do you get with

  SELECT COUNT(*) FROM Notices WHERE MATCH (Text) AGAINST ('pollution');

If air isn't indexed, I'd expect 0 hits for 'air' and 100 hits for 
'pollution'.

These are not overly common words:

mysql> select count(*) from Notices;
+--+
| count(*) |
+--+
|11990 |
+--+
1 row in set (0.00 sec)

Fix:
Use a WHERE text-column LIKE "%phrase%"  for phrase searching.
No known workaround for AND searches.

Submitter-Id:   
Originator: Joe Rhett
Organization:   
	Isite Services, Inc.

MySQL support: none
Synopsis:   Boolean mode fulltext searching fails.
Severity:   serious
Priority:   high
Category:   mysql
Class:  sw-bug
Release:mysql-4.0.16 (Source distribution)


C compiler:2.95.3
C++ compiler:  2.95.3
Environment:

System: SunOS web031 5.8 Generic_108529-23 i86pc i386 i86pc
Architecture: i86pc
Some paths:  /usr/bin/perl

Compilation info: CC='gcc'  CFLAGS=''  CXX='g++'  CXXFLAGS=''  LDFLAGS=''  ASFLAGS=''
LIBC: 
lrwxrwxrwx   1 root root  11 Sep 15 18:17 /lib/libc.so -> ./libc.so.1
-rwxr-xr-x   1 root bin   956112 Jul 29 20:10 /lib/libc.so.1
lrwxrwxrwx   1 root root  11 Sep 15 18:17 /usr/lib/libc.so -> ./libc.so.1
-rwxr-xr-x   1 root bin   956112 Jul 29 20:10 /usr/lib/libc.so.1
Configure command: ./configure '--prefix=/opt/mysql' '--localstatedir=/var/mysql' '--without-debug'




--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Bug in Boolean mode fulltext searching.

[Sergei Golubchik]

Hi!

On Jan 07, [EMAIL PROTECTED] wrote:
> >Description:
>   Boolean mode fulltext searching returns zero hits for valid queries. 
> >How-To-Repeat:
>   Create a database with a Text column.  Add a fulltext index on it.
>   Try to search for multiple words with AND or phrase syntax.

I tried - works ok.

So - examples are not enough, I need a repeatable test case (e.g. your
table data).

Regards,
Sergei

-- 
   __  ___ ___   __
  /  |/  /_ __/ __/ __ \/ /   Sergei Golubchik <[EMAIL PROTECTED]>
 / /|_/ / // /\ \/ /_/ / /__  MySQL AB, Senior Software Developer
/_/  /_/\_, /___/\___\_\___/  Osnabrueck, Germany
   <___/  www.mysql.com

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Bug in Boolean mode fulltext searching.

[jrhett]

>Description:
Boolean mode fulltext searching returns zero hits for valid queries. 
>How-To-Repeat:
Create a database with a Text column.  Add a fulltext index on it.
Try to search for multiple words with AND or phrase syntax.

Here are examples:

mysql> select Notice_ID from Notices where match (Text) against ('+pollution +control' 
in boolean mode);
Empty set (0.00 sec)

mysql> select Notice_ID from Notices where match (Text) against ('"pollution control"' 
in boolean mode);
Empty set (0.02 sec)

mysql> select Notice_ID from Notices where Text like '%pollution control%';
 
+---+
| Notice_ID |
+---+
|192090 |
+---+
1 row in set (5.00 sec)

mysql> select Notice_ID from Notices where match (Text) against ('+pollution +air' in 
boolean mode);
Empty set (0.03 sec)

mysql> select Notice_ID from Notices where match (Text) against ('"air pollution"' in 
boolean mode);
Empty set (0.00 sec)

mysql> select Notice_ID from Notices where Text like '%air pollution%';
+---+
| Notice_ID |
+---+
|196349 |
|196569 |
|188183 |
|192090 |
|192686 |
|199283 |
+---+
6 rows in set (0.17 sec)

(NOTE on the search for air -- my.cnf has ft_min_word_len=3)

All OR searches work perfectly fine, as per:

mysql> select count(*) from Notices where match (Text) against ('air pollution');
+--+
| count(*) |
+--+
|  100 |
+--+
1 row in set (0.03 sec)

These are not overly common words:

mysql> select count(*) from Notices;
+--+
| count(*) |
+--+
|11990 |
+--+
1 row in set (0.00 sec)

>Fix:
Use a WHERE text-column LIKE "%phrase%"  for phrase searching.
No known workaround for AND searches.

>Submitter-Id:  
>Originator:Joe Rhett
>Organization:  
Isite Services, Inc.
>MySQL support: none
>Synopsis:  Boolean mode fulltext searching fails.
>Severity:  serious
>Priority:  high
>Category:  mysql
>Class: sw-bug
>Release:   mysql-4.0.16 (Source distribution)

>C compiler:2.95.3
>C++ compiler:  2.95.3
>Environment:

System: SunOS web031 5.8 Generic_108529-23 i86pc i386 i86pc
Architecture: i86pc

Some paths:  /usr/bin/perl

Compilation info: CC='gcc'  CFLAGS=''  CXX='g++'  CXXFLAGS=''  LDFLAGS=''  ASFLAGS=''
LIBC: 
lrwxrwxrwx   1 root root  11 Sep 15 18:17 /lib/libc.so -> ./libc.so.1
-rwxr-xr-x   1 root bin   956112 Jul 29 20:10 /lib/libc.so.1
lrwxrwxrwx   1 root root  11 Sep 15 18:17 /usr/lib/libc.so -> ./libc.so.1
-rwxr-xr-x   1 root bin   956112 Jul 29 20:10 /usr/lib/libc.so.1
Configure command: ./configure '--prefix=/opt/mysql' '--localstatedir=/var/mysql' 
'--without-debug'


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

A series of essays on fulltext searching.

[Andrew Braithwaite]

Hi,

This isn't MySQL specific, but it's very interesting and I thought people
may be interested.

http://www.tbray.org/ongoing/When/200x/2003/07/30/OnSearchTOC

Cheers,

Andrew

SQL, Query

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: punctuation in fulltext searching

[Stephen Fromm]

> hmm well sorry to be unclear i know this works but it would return more
> results than needed also i cant expect users to add this themselves, like
i
> would have to add the astrerix to every word in that case like i do to get
> all words ;\

I don't know the MySQL issues (I'm now using some FULLTEXT indexes but
haven't played with them yet).  Note that the issue you're talking about is
one of "stemming".  The most common examples are singular versus plurals.

One solution (if the built-in MySQL stuff proves unsatisfactory) is to build
your own stemmer (assuming you're building some kind of interface to MySQL,
using the MySQL API).

-S

>
> -Original Message-
> From: Egor Egorov [mailto:[EMAIL PROTECTED]
> Sent: Tuesday, August 19, 2003 7:41 PM
> To: [EMAIL PROTECTED]
> Subject: Re: punctuation in fulltext searching
>
>
> Daniel Rossi <[EMAIL PROTECTED]> wrote:
> > Hi i have just come across an issue where a word is not being searched
up
> if there is any punctuation ie. AMROZI'S will not be search upon if you
type
> AMROZI , please help
>
> Take a look at * operator which you can use in BOOLEAN MODE.
>
>
>
> --
> 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
>
>
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:
> http://lists.mysql.com/[EMAIL PROTECTED]
>
>
> -- 
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:
http://lists.mysql.com/[EMAIL PROTECTED]
>


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

RE: punctuation in fulltext searching

[electroteque]

hmm well sorry to be unclear i know this works but it would return more
results than needed also i cant expect users to add this themselves, like i
would have to add the astrerix to every word in that case like i do to get
all words ;\

-Original Message-
From: Egor Egorov [mailto:[EMAIL PROTECTED]
Sent: Tuesday, August 19, 2003 7:41 PM
To: [EMAIL PROTECTED]
Subject: Re: punctuation in fulltext searching


Daniel Rossi <[EMAIL PROTECTED]> wrote:
> Hi i have just come across an issue where a word is not being searched up
if there is any punctuation ie. AMROZI'S will not be search upon if you type
AMROZI , please help

Take a look at * operator which you can use in BOOLEAN MODE.



--
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




--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:
http://lists.mysql.com/[EMAIL PROTECTED]


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: punctuation in fulltext searching

[Egor Egorov]

Daniel Rossi <[EMAIL PROTECTED]> wrote:
> Hi i have just come across an issue where a word is not being searched up if there 
> is any punctuation ie. AMROZI'S will not be search upon if you type AMROZI , please 
> help

Take a look at * operator which you can use in BOOLEAN MODE.



-- 
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




-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

punctuation in fulltext searching

[Daniel Rossi]

Hi i have just come across an issue where a word is not being searched up if there is 
any punctuation ie. AMROZI'S will not be search upon if you type AMROZI , please help


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: fulltext searching

[Roger Baklund]

* m n 
[...]
> SELECT * FROM `mytest` WHERE (MATCH (sub) against ("my"));

... and short (default < 4 characters) words are not indexed...

http://www.mysql.com/doc/en/Fulltext_Fine-tuning.html >

-- 
Roger

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

fulltext searching

[m n]

How fulltext is working!!?
 
I have the following table with just one record! And when I do a select
statement, mysql returns no hits!!!
 
Would you explain to me what is wrong??
 
Tanks
Cheers!
 
Adam
 
CREATE TABLE `mytest` (
  `id` int(11) NOT NULL default '0',
  `sub` text,
  PRIMARY KEY  (`id`),
  FULLTEXT KEY `sub` (`sub`)
) TYPE=MyISAM;
 
INSERT INTO `mytest` VALUES (0, 'my name is kalle and i live in the
North');
 
SELECT * FROM `mytest` WHERE (MATCH (sub) against ("my"));
 
Not hists!!!???
 

Re: fulltext searching

[Victoria Reznichenko]

"m n" <[EMAIL PROTECTED]> wrote:
> How fulltext is working!!?

>From the MySQL manual:

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 1 GB 
table.

You can read more about full-test search at:
http://www.mysql.com/doc/en/Fulltext_Search.html

> 
> I have the following table with just one record! And when I do a select
> statement, mysql returns no hits!!!
> 
> Would you explain to me what is wrong??
> 
> Tanks
> Cheers!
> 
> Adam
> 
> CREATE TABLE `mytest` (
>  `id` int(11) NOT NULL default '0',
>  `sub` text,
>  PRIMARY KEY  (`id`),
>  FULLTEXT KEY `sub` (`sub`)
> ) TYPE=MyISAM;
> 
> INSERT INTO `mytest` VALUES (0, 'my name is kalle and i live in the
> North');
> 
> SELECT * FROM `mytest` WHERE (MATCH (sub) against ("my"));
> 
> Not hists!!!???
> 
> 


-- 
For technical support contracts, goto https://order.mysql.com/?ref=ensita
This email is sponsored by Ensita.net http://www.ensita.net/
   __  ___ ___   __
  /  |/  /_ __/ __/ __ \/ /Victoria Reznichenko
 / /|_/ / // /\ \/ /_/ / /__   [EMAIL PROTECTED]
/_/  /_/\_, /___/\___\_\___/   MySQL AB / Ensita.net
   <___/   www.mysql.com





-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: fulltext searching

[Roger Baklund]

* m n
> I have the following table with just one record! And when I do a select
> statement, mysql returns no hits!!!
>
> Would you explain to me what is wrong??

You have only one row.

http://www.mysql.com/doc/en/Fulltext_Search.html >

"For very small tables, word distribution does not reflect adequately their
semantic value, and this model may sometimes produce bizarre results."

More specifically, any word found in more than 50% of the rows are
considered as a 'stopword', i.e. it does not give a hit.

All you words are in 100% of your rows... ;)

--
Roger


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: FULLTEXT Searching

[Victoria Reznichenko]

"Ralph Guzman" <[EMAIL PROTECTED]> wrote:
> I have to do a catalog search through multiple tables and columns for
> product model number, description, and name. I realize that doing
> pattern matching with multiple LIKE statements is slow so I found that
> FULLTEXT searches is a better alternative. 
> 
> I have added a FULLTEXT index to the tables I'm searching, but I get an
> unkown error when I run my query: 
> 
> SELECT p2c.categories_id, p.products_id, pd.products_name,
> p.products_quantity, p.products_image, p.products_bimage,
> p.products_price, p.products_date_added, p.products_last_modified,
> p.products_date_available, p.products_status 
> FROM products p, products_description pd, products_to_categories p2c
> WHERE MATCH(p.products_model,pd.products_name,pd.products_description)
> AGAINST('pumps') 
> AND p.products_id = pd.products_id 
> AND p.products_id = p2c.products_id 
> ORDER BY pd.products_name;
> 

In the MATCH() function you can use one or more columns included in the FULLTEXT 
index, but you doesn't have FULLTEXT index on these three columns from different 
tables.

You can mix columns from different tables if you use full-text search in BOOLEAN MODE 
without full-text index.


-- 
For technical support contracts, goto https://order.mysql.com/?ref=ensita
This email is sponsored by Ensita.net http://www.ensita.net/
   __  ___ ___   __
  /  |/  /_ __/ __/ __ \/ /Victoria Reznichenko
 / /|_/ / // /\ \/ /_/ / /__   [EMAIL PROTECTED]
/_/  /_/\_, /___/\___\_\___/   MySQL AB / Ensita.net
   <___/   www.mysql.com





-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: FULLTEXT Searching

[Cybot]

Ralph Guzman wrote:

I have to do a catalog search through multiple tables and columns for
product model number, description, and name. I realize that doing
pattern matching with multiple LIKE statements is slow so I found that
FULLTEXT searches is a better alternative. 

I have added a FULLTEXT index to the tables I'm searching, but I get an
unkown error when I run my query: 
what means 'unknown error' ? did MySQL tell you 'unknown error'?
or do you get no results? or a timeout? or whatever?
--
Sebastian Mendel
www.sebastianmendel.de
www.tekkno4u.de
www.nofetish.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

FULLTEXT Searching

[Ralph Guzman]

I have to do a catalog search through multiple tables and columns for
product model number, description, and name. I realize that doing
pattern matching with multiple LIKE statements is slow so I found that
FULLTEXT searches is a better alternative. 

I have added a FULLTEXT index to the tables I'm searching, but I get an
unkown error when I run my query: 

SELECT p2c.categories_id, p.products_id, pd.products_name,
p.products_quantity, p.products_image, p.products_bimage,
p.products_price, p.products_date_added, p.products_last_modified,
p.products_date_available, p.products_status 
FROM products p, products_description pd, products_to_categories p2c
WHERE MATCH(p.products_model,pd.products_name,pd.products_description)
AGAINST('pumps') 
AND p.products_id = pd.products_id 
AND p.products_id = p2c.products_id 
ORDER BY pd.products_name;

This is how my table structures look:

CREATE TABLE `products` (
  `products_id` int(11) NOT NULL auto_increment,
  `products_quantity` int(4) NOT NULL default '0',
  `products_model` varchar(12) default NULL,
  `products_image` varchar(64) default 'image_na.gif',
  `products_bimage` varchar(64) default 'image_na.jpg',
  `products_price` decimal(15,4) NOT NULL default '0.',
  `products_date_added` datetime NOT NULL default '-00-00 00:00:00',
  `products_last_modified` datetime default NULL,
  `products_date_available` datetime default NULL,
  `products_weight` decimal(5,2) NOT NULL default '0.00',
  `products_status` tinyint(1) NOT NULL default '0',
  `products_tax_class_id` int(11) NOT NULL default '0',
  `manufacturers_id` int(11) default NULL,
  `products_ordered` int(11) NOT NULL default '0',
  PRIMARY KEY  (`products_id`),
  KEY `idx_products_date_added` (`products_date_added`),
  FULLTEXT KEY `products_model` (`products_model`)
) TYPE=MyISAM AUTO_INCREMENT=928 ;

CREATE TABLE `products_description` (
  `products_id` int(11) NOT NULL auto_increment,
  `language_id` int(11) NOT NULL default '1',
  `products_name` varchar(64) NOT NULL default '',
  `products_description` text,
  `products_url` varchar(255) default NULL,
  `products_viewed` int(5) default '0',
  PRIMARY KEY  (`products_id`,`language_id`),
  KEY `products_name` (`products_name`),
  FULLTEXT KEY `products_name_2`
(`products_name`,`products_description`)
) TYPE=MyISAM AUTO_INCREMENT=928 ;





-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

RE: fulltext searching and query order question

[H M Kunzmann]

I think I need to clarify :

The fulltext indexing & searching here works great.
The search completes in good time, but then I want it in a different
order, as described by index1 down below. This resorting step is the one
that takes forever, not the fulltext search.

So essentially, a FORCE INDEX(index1) is more appropriate, but if I do
that, then the order is fast, but the fulltext search takes forever !




On Fri, 2003-06-13 at 18:32, Mike Hillyer wrote:
> Have you tried adding force index on your fulltext index?
> 
> Something similar to this:
> 
> select field1,field2,field3 from table FORCE INDEX(index2)
>   where field1='something'
>   and field2='something_else'
>   and field3='something_more'
>   and match(field4) against ('word1 word2' in boolean mode)
>   order by field1,field2,field3
> 
> Regards,
> Mike Hillyer
> www.vbmysql.com
> 
> 
> -Original Message-
> From: H M Kunzmann [mailto:[EMAIL PROTECTED] 
> Sent: Friday, June 13, 2003 10:33 AM
> To: [EMAIL PROTECTED]
> Subject: fulltext searching and query order question
> 
> 
> 
> Hi all.
> 
> I have a fulltext index on a table.
> 
> If I have the following fields:
> 
> field1,field2,field3,field4
> 
> Field4 being the fulltext field.
> 
> I have the following indices: 
> 
> index1->field1,field2,field3
> index2->fulltext field4
> 
> If I do a select:
> select * from table where match(index2) 
>   against ('word1 word2' in boolean mode);
> 
> I get a very fast result.
> 
> Essentially I want to do the following:
> 
> If I do a select field1,field2,field3 from table
>   where field1='something'
>   and field2='something_else'
>   and field3='something_more'
>   and match(field4) against ('word1 word2' in boolean mode)
>   order by field1,field2,field3
> 
> I can't seem to get it right that the query can return
> quickly, as it does a table scan to sort the table,
> which takes forever.
> 
> How do I get a fulltext search to be able to sort according
> to a different field ???
> 
> Thanks for all assistance :-)
> 
> Regards,
> Herbert
> 
> 


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: REPOST: FULLTEXT searching help needed. Please somebody help.

[Sergei Golubchik]

Hi!

On Jun 13, H M Kunzmann wrote:
> The answer is that a fulltext index can only be built on
> a TEXT field. Even though the mysql documentation describes
> MEDIUMTEXT and LONGTEXT fields as 'BLOB or TEXT field that can hold..',
> they can not be used.

No, this is wrong.
Any xxxTEXT field can be used in FULLTEXT index.
TEXT, MEDIUMTEXT, LONGTEXT, TINYTEXT - they all work.
 
Regards,
Sergei

-- 
MySQL Development Team
   __  ___ ___   __
  /  |/  /_ __/ __/ __ \/ /   Sergei Golubchik <[EMAIL PROTECTED]>
 / /|_/ / // /\ \/ /_/ / /__  MySQL AB, http://www.mysql.com/
/_/  /_/\_, /___/\___\_\___/  Osnabrueck, Germany
   <___/

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

RE: fulltext searching and query order question

[Mike Hillyer]

Have you tried adding force index on your fulltext index?

Something similar to this:

select field1,field2,field3 from table FORCE INDEX(index2)
  where field1='something'
  and field2='something_else'
  and field3='something_more'
  and match(field4) against ('word1 word2' in boolean mode)
  order by field1,field2,field3

Regards,
Mike Hillyer
www.vbmysql.com


-Original Message-
From: H M Kunzmann [mailto:[EMAIL PROTECTED] 
Sent: Friday, June 13, 2003 10:33 AM
To: [EMAIL PROTECTED]
Subject: fulltext searching and query order question



Hi all.

I have a fulltext index on a table.

If I have the following fields:

field1,field2,field3,field4

Field4 being the fulltext field.

I have the following indices: 

index1->field1,field2,field3
index2->fulltext field4

If I do a select:
select * from table where match(index2) 
against ('word1 word2' in boolean mode);

I get a very fast result.

Essentially I want to do the following:

If I do a select field1,field2,field3 from table
  where field1='something'
  and field2='something_else'
  and field3='something_more'
  and match(field4) against ('word1 word2' in boolean mode)
  order by field1,field2,field3

I can't seem to get it right that the query can return
quickly, as it does a table scan to sort the table,
which takes forever.

How do I get a fulltext search to be able to sort according
to a different field ???

Thanks for all assistance :-)

Regards,
Herbert



-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: REPOST: FULLTEXT searching help needed. Please somebody help.

[H M Kunzmann]

The answer is that a fulltext index can only be built on
a TEXT field. Even though the mysql documentation describes
MEDIUMTEXT and LONGTEXT fields as 'BLOB or TEXT field that can hold..',
they can not be used.



On Thu, 2003-06-05 at 09:59, H M Kunzmann wrote:
> Hello All.
> 
> I am using Redhat 9.0 with MySQL 4.0.12-0.
> 
> I've hit something of a dead-end with fulltext searching and I don't
> know where to look next. 
> 
> I have a table that is about 1.5GB with about 400 records.
> As you can tell, every record is about 4MB, all of which is
> text.
> 
> I've created a fulltext index on the table, with 
> > alter table table2002 add fulltext data (data);
> After this is done (takes about 20 minutes to do) I check
> out the index size, which is 8,722 KB...
> 
> If I do a fulltext search against this table, it takes about
> 1.5 minutes.
> > select filename from table2002 where match(data) against ('whatever')
> 
> If I do an explain on my query, it confirms that the index is used
> +--+-+-++---+---++---+
> |table |type |possible_keys|key |key_len|ref|rows|extra  |
> +--+-+-++---+---++---+
> |table2002 |fulltext |data |data|  0|   |  1 |using where|
> +--+-+-++---+---++---+
> 
> I have another table, sized at only about 50MB, which I use the same
> query and index on, which takes less than a second to do. One thing I
> have noticed, is that the index size on this much smaller table is
> larger than the bigger table (about 9MB).
> 
> This is a shot in the dark, but it seems to me that the index size is
> _way_ too small and as such obviously cannot really help. If this is the
> case, how do I increase it ?
> 
> Here are some of my mysql settings:
> max_sort_length=16M
> table_cache=256M
> key_buffer=128M
> sort_buffer=4M
> read_buffer_size=1M
> 
> Should I set anything else ? 
> What can I do to get respectable return times ?
> 
> Please lend me a helping hand...
> Thanks in advance.
> H M Kunzmann
-- 
Herbert Michael Kunzmann
Binary Chaos Magician


signature.asc
Description: This is a digitally signed message part

fulltext searching and query order question

[H M Kunzmann]

Hi all.

I have a fulltext index on a table.

If I have the following fields:

field1,field2,field3,field4

Field4 being the fulltext field.

I have the following indices: 

index1->field1,field2,field3
index2->fulltext field4

If I do a select:
select * from table where match(index2) 
against ('word1 word2' in boolean mode);

I get a very fast result.

Essentially I want to do the following:

If I do a select field1,field2,field3 from table
  where field1='something'
  and field2='something_else'
  and field3='something_more'
  and match(field4) against ('word1 word2' in boolean mode)
  order by field1,field2,field3

I can't seem to get it right that the query can return
quickly, as it does a table scan to sort the table,
which takes forever.

How do I get a fulltext search to be able to sort according
to a different field ???

Thanks for all assistance :-)

Regards,
Herbert



-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: REPOST: FULLTEXT searching help needed. Please somebody help.

[H M Kunzmann]

> > When mysql is indexing You can check your data file direcory and You 
> > ca see that one file (the index) is growing in size.
> I see it grow up to 8M and it stays there.
Watching this, it grows very slowly. After 5 minutes of indexing, the
file size has barely hit 2MB.

> > The command I use is:
> > create fulltext index  Name on Table(field)
> So far I've only used the alter table call... I will use this call
> next and see if I get any problems.
I've used the create index call now, as described above, and it does not
change anything. The index still only grows to 8MB.

> I've read something of MySQL 4.0.12-0 having a fulltext index bug that
> converts text into char. (MySQL documentation in MySQL 4.0.13-0 docs
> describes this in a bug fix entry. Perhaps this is a symptom ?
> I am busy downloading the newer version to find out.
No change. 

> Can your system handle files larger than 2GB?
Maybe this is the problem. Can anyone give me any indication as 
to whether this may be the case ? Should I load the kernel that 
supports bigger filesystems ?

Thanks for all help

Ciao
H M Kunzmann


signature.asc
Description: This is a digitally signed message part

Re: REPOST: FULLTEXT searching help needed. Please somebody help.

[H M Kunzmann]

> I think your index is corrupted because I expect a 1.5 GB index and not 8M!
> You can see word list wit a utility (sorry I don't remember te name ft_dump).

I agree with this :-)

> I suggest You to drop fulltext index, duplicate database and remove 
> some rrecords.
> Then create index index again.

I've done this a couple of times already with the same result.

> Some questions:
> Do You have disk space to index?
I have 22GB free.

> Can your system handle files larger than 2GB?
I'm not sure. Perhaps not with my current kernel. I am using the
standard RH9 kernel.

> When mysql is indexing You can check your data file direcory and You 
> ca see that one file (the index) is growing in size.
I see it grow up to 8M and it stays there.

> The command I use is:
> create fulltext index  Name on Table(field)
So far I've only used the alter table call... I will use this call
next and see if I get any problems.

I've read something of MySQL 4.0.12-0 having a fulltext index bug that
converts text into char. (MySQL documentation in MySQL 4.0.13-0 docs
describes this in a bug fix entry. Perhaps this is a symptom ?
I am busy downloading the newer version to find out.

Thank you for your help.
I'll get back to you on whether any of the above helped.


signature.asc
Description: This is a digitally signed message part

RE: REPOST: FULLTEXT searching help needed. Please somebody help.

[electroteque]

create fulltext index  Name on Table(field) i didnt get this

i usually do add fulltext field (field) i think , is that wrong ??

-Original Message-
From: Santino [mailto:[EMAIL PROTECTED]
Sent: Thursday, June 05, 2003 6:14 PM
To: [EMAIL PROTECTED]
Subject: Re: REPOST: FULLTEXT searching help needed. Please somebody
help.


I think your index is corrupted because I expect a 1.5 GB index and not 8M!
You can see word list wit a utility (sorry I don't remember te name
ft_dump).

I suggest You to drop fulltext index, duplicate database and remove
some rrecords.
Then create index index again.

Some questions:
Do You have disk space to index?
Can your system handle files larger than 2GB?

When mysql is indexing You can check your data file direcory and You
ca see that one file (the index) is growing in size.

The command I use is:
create fulltext index  Name on Table(field)

I have some tables with about 300.000 records (about 2k each) and all
works fine:
I rebuild index in minutes and the query needs only 1 or 2  seconds.
Santino

At 9:59 +0200 5-06-2003, H M Kunzmann wrote:
>Hello All.
>
>I am using Redhat 9.0 with MySQL 4.0.12-0.
>
>I've hit something of a dead-end with fulltext searching and I don't
>know where to look next.
>
>I have a table that is about 1.5GB with about 400 records.
>As you can tell, every record is about 4MB, all of which is
>text.
>
>I've created a fulltext index on the table, with
>>  alter table table2002 add fulltext data (data);
>After this is done (takes about 20 minutes to do) I check
>out the index size, which is 8,722 KB...
>
>If I do a fulltext search against this table, it takes about
>1.5 minutes.
>>  select filename from table2002 where match(data) against ('whatever')
>
>If I do an explain on my query, it confirms that the index is used
>+--+-+-++---+---++---+
>|table |type |possible_keys|key |key_len|ref|rows|extra  |
>+--+-+-++---+---++---+
>|table2002 |fulltext |data |data|  0|   |  1 |using where|
>+--+-+-++---+---++---+
>
>I have another table, sized at only about 50MB, which I use the same
>query and index on, which takes less than a second to do. One thing I
>have noticed, is that the index size on this much smaller table is
>larger than the bigger table (about 9MB).
>
>This is a shot in the dark, but it seems to me that the index size is
>_way_ too small and as such obviously cannot really help. If this is the
>case, how do I increase it ?
>
>Here are some of my mysql settings:
>max_sort_length=16M
>table_cache=256M
>key_buffer=128M
>sort_buffer=4M
>read_buffer_size=1M
>
>Should I set anything else ?
>What can I do to get respectable return times ?
>
>Please lend me a helping hand...
>Thanks in advance.
>H M Kunzmann
>
>
>--
>MySQL General Mailing List
>For list archives: http://lists.mysql.com/mysql
>To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:
http://lists.mysql.com/[EMAIL PROTECTED]


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: REPOST: FULLTEXT searching help needed. Please somebody help.

[Santino]

I think your index is corrupted because I expect a 1.5 GB index and not 8M!
You can see word list wit a utility (sorry I don't remember te name ft_dump).
I suggest You to drop fulltext index, duplicate database and remove 
some rrecords.
Then create index index again.

Some questions:
Do You have disk space to index?
Can your system handle files larger than 2GB?
When mysql is indexing You can check your data file direcory and You 
ca see that one file (the index) is growing in size.

The command I use is:
create fulltext index  Name on Table(field)
I have some tables with about 300.000 records (about 2k each) and all 
works fine:
I rebuild index in minutes and the query needs only 1 or 2  seconds.
Santino

At 9:59 +0200 5-06-2003, H M Kunzmann wrote:
Hello All.

I am using Redhat 9.0 with MySQL 4.0.12-0.

I've hit something of a dead-end with fulltext searching and I don't
know where to look next.
I have a table that is about 1.5GB with about 400 records.
As you can tell, every record is about 4MB, all of which is
text.
I've created a fulltext index on the table, with
 alter table table2002 add fulltext data (data);
After this is done (takes about 20 minutes to do) I check
out the index size, which is 8,722 KB...
If I do a fulltext search against this table, it takes about
1.5 minutes.
 select filename from table2002 where match(data) against ('whatever')
If I do an explain on my query, it confirms that the index is used
+--+-+-++---+---++---+
|table |type |possible_keys|key |key_len|ref|rows|extra  |
+--+-+-++---+---++---+
|table2002 |fulltext |data |data|  0|   |  1 |using where|
+--+-+-++---+---++---+
I have another table, sized at only about 50MB, which I use the same
query and index on, which takes less than a second to do. One thing I
have noticed, is that the index size on this much smaller table is
larger than the bigger table (about 9MB).
This is a shot in the dark, but it seems to me that the index size is
_way_ too small and as such obviously cannot really help. If this is the
case, how do I increase it ?
Here are some of my mysql settings:
max_sort_length=16M
table_cache=256M
key_buffer=128M
sort_buffer=4M
read_buffer_size=1M
Should I set anything else ?
What can I do to get respectable return times ?
Please lend me a helping hand...
Thanks in advance.
H M Kunzmann
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

REPOST: FULLTEXT searching help needed. Please somebody help.

[H M Kunzmann]

Hello All.

I am using Redhat 9.0 with MySQL 4.0.12-0.

I've hit something of a dead-end with fulltext searching and I don't
know where to look next. 

I have a table that is about 1.5GB with about 400 records.
As you can tell, every record is about 4MB, all of which is
text.

I've created a fulltext index on the table, with 
> alter table table2002 add fulltext data (data);
After this is done (takes about 20 minutes to do) I check
out the index size, which is 8,722 KB...

If I do a fulltext search against this table, it takes about
1.5 minutes.
> select filename from table2002 where match(data) against ('whatever')

If I do an explain on my query, it confirms that the index is used
+--+-+-++---+---++---+
|table |type |possible_keys|key |key_len|ref|rows|extra  |
+--+-+-++---+---++---+
|table2002 |fulltext |data |data|  0|   |  1 |using where|
+--+-+-++---+---++---+

I have another table, sized at only about 50MB, which I use the same
query and index on, which takes less than a second to do. One thing I
have noticed, is that the index size on this much smaller table is
larger than the bigger table (about 9MB).

This is a shot in the dark, but it seems to me that the index size is
_way_ too small and as such obviously cannot really help. If this is the
case, how do I increase it ?

Here are some of my mysql settings:
max_sort_length=16M
table_cache=256M
key_buffer=128M
sort_buffer=4M
read_buffer_size=1M

Should I set anything else ? 
What can I do to get respectable return times ?

Please lend me a helping hand...
Thanks in advance.
H M Kunzmann


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

re: Fulltext searching - getting list of stop words?

[Egor Egorov]

Tim,
Thursday, September 19, 2002, 1:09:42 PM, you wrote:

TF> I'm implementing a search feature on one of my sites and I would like
TF> to have a Google-style "X is a very common word and was not included
TF> in your search" for stop words.  I believe the stop words in fulltext
TF> searching are dictated at compile time, so is there any way I can get
TF> a list of current stop words from MySQL?

Check /myisam/ft_static.c from 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

Fulltext searching - getting list of stop words?

[Tim Fountain]

I'm implementing a search feature on one of my sites and I would like
to have a Google-style "X is a very common word and was not included
in your search" for stop words.  I believe the stop words in fulltext
searching are dictated at compile time, so is there any way I can get
a list of current stop words from MySQL?

Alternatively, how would I find out the default (assuming there is
one) set of stop words used?


Cheers,
Tim.

-- 
Tim Fountain ([EMAIL PROTECTED])
http://www.tfountain.co.uk/


-
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

Re: fulltext searching / BUG report

[Marko Djukic]

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 <[EMAIL PROTECTED]>:

> 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 <[EMAIL PROTECTED]>
>  / /|_/ / // /\ \/ /_/ / /__  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 <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Re: fulltext searching / BUG report

[Sergei Golubchik]

Hi!

On Jul 02, Thomas Spahni wrote:
> On Tue, 2 Jul 2002, Erlend Hops? Str?msvik wrote:
> 
> > Download the 4.0.2 source and compile it. 
> > Things seem to work a lot better with the 4.0.2. And it won't crash with
> > special combinations of words :)
> 
> Hi,
> 
> Some things with BOOLEAN MODE seem still broken. Especially the '*' 
> jokers. I have 4.0.2 taken from the source tree yesterday. I'm querying
> against a table with 14579 documents containing 187621564 bytes of data
> and a fulltext index.
> 
...

> SELECT doc FROM plaintext \
> WHERE MATCH (bgetxt) AGAINST ('integr* ad?qu*' IN BOOLEAN MODE);
> 
> Conclusion:
> 
> combinations of 2 words with asterisk joker IN BOOLEAN MODE
> do not work.

Hmm...
According to the mysql-test/t/fulltext.test it DOES work:
select * from t1 where MATCH a,b AGAINST ("+call* +coll*" IN BOOLEAN MODE);

I've also tried the same query w/o '+' (to make it similar to yours) -
it worked also.

Could you try to create a test case for the bug ?

Regards,
Sergei

-- 
MySQL Development Team
   __  ___ ___   __
  /  |/  /_ __/ __/ __ \/ /   Sergei Golubchik <[EMAIL PROTECTED]>
 / /|_/ / // /\ \/ /_/ / /__  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 <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Re: fulltext searching

[Eivind A. Sivertsen]

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

RE: fulltext searching / BUG report

[Thomas Spahni]

On Tue, 2 Jul 2002, Erlend Hopsø Strømsvik wrote:

> Download the 4.0.2 source and compile it. 
> Things seem to work a lot better with the 4.0.2. And it won't crash with
> special combinations of words :)

Hi,

Some things with BOOLEAN MODE seem still broken. Especially the '*' 
jokers. I have 4.0.2 taken from the source tree yesterday. I'm querying
against a table with 14579 documents containing 187621564 bytes of data
and a fulltext index.

Table structure is:
CREATE TABLE `plaintext` (
  `id` int(11) NOT NULL auto_increment,
  `doc` varchar(16) NOT NULL default '',
  `code` int(10) unsigned NOT NULL default '0',
  `part` tinyint(4) NOT NULL default '0',
  `bgetxt` text,
  PRIMARY KEY  (`id`),
  KEY `doc` (`doc`),
  KEY `code` (`code`),
  FULLTEXT KEY `bgetxt` (`bgetxt`)
) TYPE=MyISAM

Bug example:

select doc from plaintext \
where match(bgetxt) against ('integrität');

yields:

171 rows in set (0.02 sec)

and:

SELECT doc FROM plaintext \
WHERE MATCH(BGETXT) AGAINST ('integrität adäquanz');

yields:

198 rows in set (0.02 sec)

6 of these rows contain both words:

SELECT doc FROM plaintext \
WHERE MATCH(BGETXT) AGAINST ('+integrität +adäquanz' IN BOOLEAN MODE);

yields:

+---+
| doc   |
+---+
| 112 V 30  |
| 115 V 133 |
| 117 V 359 |
| 117 V 369 |
| 118 V 286 |
| 119 V 335 |
+---+
6 rows in set (0.01 sec)

but now look at this:

There are lots of possible endings for my search words:

integrität integritätsschaden adäquat adäquanz adäquate ... (many more)

SELECT doc FROM plaintext \
WHERE MATCH (bgetxt) AGAINST ('integr*' IN BOOLEAN MODE);

gives:

997 rows in set (0.11 sec)

this looks is still good! Many additional hits are found.

Therefore I do:

SELECT doc FROM plaintext \
WHERE MATCH (bgetxt) AGAINST ('integr* adäqu*' IN BOOLEAN MODE);

and I get:

+--+
| doc  |
+--+
| 117 V 71 |
+--+
1 row in set (2.46 sec)

The text which has been found contains text beginning with:

--
10. Urteil vom 11. Januar 1991 i.S. X gegen Bundesamt für
Militärversicherung und Versicherungsgericht des Kantons Solothurn 

Regeste

Art. 23 Abs. 1 und Art. 25 Abs. 1 MVG: Bemessung des
Integritätsschadens und Beginn der Integritätsrente.

- Bemessung des Integritätsschadens (Zusammenfassung der
Rechtsprechung; Erw. 3a).

- Die Beeinträchtigung der Integrität bemisst sich an den Folgen,
welche die geschädigte Gesundheit auf primäre Lebensfunktionen hat
(Erw. 3a/bb/aaa).

- Der Integritätsschadensgrad kann 60% übersteigen, richtet sich
jedoch
weder direkt noch analogieweise nach den Ansätzen gemäss Anhang 3 zur UVV
(Bestätigung der Rechtsprechung; Erw. 3c/aa).

- Bemessung des Integritätsschadens bei mehreren körperlichen 


Conclusion:

combinations of 2 words with asterisk joker IN BOOLEAN MODE
do not work.

Thomas Spahni
-- 
sql, query


-
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

Re: fulltext searching

[Marko Djukic]

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=UTF-8

ciao,

m.

Quoting "Eivind A. Sivertsen" <[EMAIL PROTECTED]>:

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

Re: fulltext searching

[Marko Djukic]

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" <[EMAIL PROTECTED]>:

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

Re: Re: fulltext searching

[Egor Egorov]

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
 / /|_/ / // /\ \/ /_/ / /__   [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

Re: fulltext searching

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

RE: fulltext searching

[Peter Lovatt]

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

Re: fulltext searching

[Marko Djukic]

section 6.8 in the manual i think...

Quoting "Eivind A. Sivertsen" <[EMAIL PROTECTED]>:

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

Re: fulltext searching

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

RE: fulltext searching

[Erlend Hopsø Strømsvik]

I've never gotten the boolean features to work with version 4.0.1-alpha of
MySQL. 
Also the database will crash with some queries where some of the search
words are the same as words in the stopword list (myisam/ft_static.c in the
sourcecode)

Download the 4.0.2 source and compile it. 
Things seem to work a lot better with the 4.0.2. And it won't crash with
special combinations of words :)


Erlend Stromsvik


-Original Message-
From: Marko Djukic [mailto:[EMAIL PROTECTED]]
Sent: 1. juli 2002 12:43
To: [EMAIL PROTECTED]
Subject: Re: fulltext searching 


hi all...

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 an sql query like this:
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 mysql 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 <[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

Re: fulltext searching

[Sergei Golubchik]

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 <[EMAIL PROTECTED]>
 / /|_/ / // /\ \/ /_/ / /__  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 <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Re: fulltext searching

[Marko Djukic]

hi all...

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 an sql query like this:
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 mysql 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 <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

mySQL 4 - FullText searching syntax?

[Alain Fontaine - Consultant and developer]

  Hello,

  I am evaluating mySQL 4.0.1-alpha on Redhat Linux 7.2 (RPM version). I am
playing with the new fulltext search features and I have a question
regarding the syntax...

  I have created a fulltext index on a field 'Notes'. I'd like to search for
records that have words starting with "appartemen" in them, but NOT words
starting with "lux", and neither with "prop". So I thought this would do the
trick:

<-- snip -->
select
  BienID
from
  biens
where
  MATCH(Notes) AGAINST('+appartemen* -lux* -prop*' IN BOOLEAN MODE)
<-- snip -->

  This does not produce the correct result, whereas this does:

<-- snip -->
select
  BienID
from
  biens
where
  MATCH(Notes) AGAINST('appartemen*' IN BOOLEAN MODE)
AND
  NOT MATCH(Notes) AGAINST('lux*' IN BOOLEAN MODE)
AND
  NOT MATCH(Notes) AGAINST('prop*' IN BOOLEAN MODE)
<-- snip -->

  What have I understood wrong?

---
Alain Fontaine
Consultant & Developer
VAlain S.A.
Tél: +32-4-2522950
---


-
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

Re: mySQL 4 - FullText searching syntax?

[Sergei Golubchik]

Hi!

On Feb 07, Alain Fontaine - Consultant and developer wrote:
> 
> <-- snip -->
> select
>   BienID
> from
>   biens
> where
>   MATCH(Notes) AGAINST('+appartemen* -lux* -prop*' IN BOOLEAN MODE)
> <-- snip -->
> 
>   This does not produce the correct result, whereas this does:
> 
> <-- snip -->
> select
>   BienID
> from
>   biens
> where
>   MATCH(Notes) AGAINST('appartemen*' IN BOOLEAN MODE)
> AND
>   NOT MATCH(Notes) AGAINST('lux*' IN BOOLEAN MODE)
> AND
>   NOT MATCH(Notes) AGAINST('prop*' IN BOOLEAN MODE)
> <-- snip -->

That looks like a bug, can you create a test case for it ?

Regards,
Sergei

-- 
MySQL Development Team
   __  ___ ___   __
  /  |/  /_ __/ __/ __ \/ /   Sergei Golubchik <[EMAIL PROTECTED]>
 / /|_/ / // /\ \/ /_/ / /__  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 <[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

Re: mySQL 4 - FullText searching syntax?

[Sergei Golubchik]

Hi!

On Feb 07, Alain Fontaine - Consultant and developer wrote:
> 
> <-- snip -->
> select
>   BienID
> from
>   biens
> where
>   MATCH(Notes) AGAINST('+appartemen* -lux* -prop*' IN BOOLEAN MODE)
> <-- snip -->
> 
>   This does not produce the correct result, whereas this does:
> 
> <-- snip -->
> select
>   BienID
> from
>   biens
> where
>   MATCH(Notes) AGAINST('appartemen*' IN BOOLEAN MODE)
> AND
>   NOT MATCH(Notes) AGAINST('lux*' IN BOOLEAN MODE)
> AND
>   NOT MATCH(Notes) AGAINST('prop*' IN BOOLEAN MODE)
> <-- snip -->

That looks like a bug, can you create a test case for it ?

Regards,
Sergei

-- 
MySQL Development Team
   __  ___ ___   __
  /  |/  /_ __/ __/ __ \/ /   Sergei Golubchik <[EMAIL PROTECTED]>
 / /|_/ / // /\ \/ /_/ / /__  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 <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

mySQL 4 - FullText searching syntax?

[Alain Fontaine - Consultant and developer]

  Hello,

  I am evaluating mySQL 4.0.1-alpha on Redhat Linux 7.2 (RPM version). I am
playing with the new fulltext search features and I have a question
regarding the syntax...

  I have created a fulltext index on a field 'Notes'. I'd like to search for
records that have words starting with "appartemen" in them, but NOT words
starting with "lux", and neither with "prop". So I thought this would do the
trick:

<-- snip -->
select
  BienID
from
  biens
where
  MATCH(Notes) AGAINST('+appartemen* -lux* -prop*' IN BOOLEAN MODE)
<-- snip -->

  This does not produce the correct result, whereas this does:

<-- snip -->
select
  BienID
from
  biens
where
  MATCH(Notes) AGAINST('appartemen*' IN BOOLEAN MODE)
AND
  NOT MATCH(Notes) AGAINST('lux*' IN BOOLEAN MODE)
AND
  NOT MATCH(Notes) AGAINST('prop*' IN BOOLEAN MODE)
<-- snip -->

  What have I understood wrong?

---
Alain Fontaine
Consultant & Developer
VAlain S.A.
Tél: +32-4-2522950
---


-
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

RE: Phrase based fulltext searching

[Roger . Bennett]

A relatively straightforward way round this - and a number of other problems
with FULLTEXT - is to combine a full text search with a LIKE criterion to
narrow the results. Example (assuming that you are searching a table called
Documents on a TEXT field called Term):

SELECT * FROM Documents WHERE MATCH (Term) AGAINST ('search phrase') AND
Term LIKE '%search phrase%'

This, in my experience, appears to be nearly as fast (in perceived terms) as
the straight full text search because the LIKE criterion, whilst slow on
large databases if used with a % wildcard at the beginning (which prevents
use of any conventional indexes), is only applied here on the set matching
the fulltext criterion. It should therefore only be really slow in extreme
conditions where the number of entries matching the fulltext criterion is
very high. Make sure you write the query in this order, though - I would by
no means guarantee that MySQL will optimise it if you write:

SELECT * FROM Documents WHERE Term LIKE '%search phrase%' AND MATCH (Term)
AGAINST ('search phrase')

If optimisation does not take place, this query could easily be several
orders of magnitude slower than the correctly sequenced version!

Roger Bennett

-
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

Re: More questions about Fulltext searching.

[Matthew Brealey]

 --- Stoyan <[EMAIL PROTECTED]> wrote: > hi,
> 
> why don't you try to use this:
> 
> SELECT * FROM BOOK WHERE author LIKE '%charles%dickens%';

That query takes 6.4 seconds. My query (MATCH author AGAINST ('charles')
and match author against('dickens')) takes 1.3 seconds. Fulltext searching
is MUCH more efficient.

=



Do You Yahoo!?
Get your free @yahoo.co.uk address at http://mail.yahoo.co.uk
or your free @yahoo.ie address at http://mail.yahoo.ie

-
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

Re again: More questions about Fulltext searching.

[Nessi]

Hello Matthew,

I posted this earlier today but maybe you didnt get the message.
This definitely works for me, and its definitely an AND search (example):

SELECT author, title, year, recommend, copy, publ_desc, publ_loc, 
CEILING(IF(MATCH author AGAINST ( 'Adams') > 0, IF(MATCH title AGAINST 
('Places') > 0, IF(MATCH publ_desc AGAINST ('Society') > 0, (MATCH author 
AGAINST ('Adams') + MATCH title AGAINST ('Places') + MATCH publ_desc 
AGAINST ('Society')), 0), 0), 0)) as x FROM books, publ WHERE publ_id = 
pub_id ORDER BY x DESC

Its messy and you will need to split the words if you only use 1 entry 
field (I use 3 different ones).
But that shouldnt be a problem in php with explode function (even for 
whitespaces).
You then need a function to create the seperate parts according to the 
number of
the words. Even this example select query is only from a test file, my 
original file
sets the whole query together in a php process.
"as x" will give only more than 0% if all three words apply.
So you only need a function in your script reading out the rows with x > 0.

See also the IF() section in the mysql manual...thats how I came up with 
this query.

Hope this helps

Cheers, Nessi



At 17:08 27/06/01 , you wrote:
>  --- Colin Faber <[EMAIL PROTECTED]> wrote:
> > Ok,
> >
> > The message set that I noticed this morning sparked my interest in this.
> >
> > Currently I'm developing a search engine that will utilize mysql's
> > fulltext match technology. The problem that i've run into is, I can't
> > seem to find any documentation on how to force an AND search with in
> > MATCH AGAINST syntax, As far as I can tell this is `OR' only. Has is
> > there
> > an option to force AND?
>
>Sadly not. I've had the same trouble as you with this. It's mentioned
>somewhere
>(http://www.mysql.com/doc/F/u/Fulltext_Features_to_Appear_in_MySQL_4.0.html)
>that this will be in v4; until then, no chance.
>
>The way I do this is to split queries into words and do AND.
>
>So to find charles dickens:
>
>SELECT * FROM BOOK WHERE MATCH author AGAINST ('charles') AND MATCH author
>AGAINST ('dickens')
>
>If anyone knows how to make 'charles dickens' match simply fields
>containing both those words, rather than one or the other (ordering by
>score helps, but still the last results would be irrelevant), please
>enlighten me.
>
>=
>
>
>
>Do You Yahoo!?
>Get your free @yahoo.co.uk address at http://mail.yahoo.co.uk
>or your free @yahoo.ie address at http://mail.yahoo.ie
>
>-
>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

Re: More questions about Fulltext searching.

[Matthew Brealey]

 --- Colin Faber <[EMAIL PROTECTED]> wrote: 
> Ok, 
> 
> The message set that I noticed this morning sparked my interest in this.
> 
> Currently I'm developing a search engine that will utilize mysql's 
> fulltext match technology. The problem that i've run into is, I can't
> seem to find any documentation on how to force an AND search with in
> MATCH AGAINST syntax, As far as I can tell this is `OR' only. Has is
> there
> an option to force AND? 

Sadly not. I've had the same trouble as you with this. It's mentioned
somewhere
(http://www.mysql.com/doc/F/u/Fulltext_Features_to_Appear_in_MySQL_4.0.html)
that this will be in v4; until then, no chance.

The way I do this is to split queries into words and do AND.

So to find charles dickens:

SELECT * FROM BOOK WHERE MATCH author AGAINST ('charles') AND MATCH author
AGAINST ('dickens')

If anyone knows how to make 'charles dickens' match simply fields
containing both those words, rather than one or the other (ordering by
score helps, but still the last results would be irrelevant), please
enlighten me.

=



Do You Yahoo!?
Get your free @yahoo.co.uk address at http://mail.yahoo.co.uk
or your free @yahoo.ie address at http://mail.yahoo.ie

-
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

Re: More questions about Fulltext searching.

[Nessi]

>Hi
>
>I have posted this before. You can do an AND search by using the IF() 
>function.
>Its messy in the query but it works.
>Here an example of my select query - 3 inputs are checked as AND:
>
>
>SELECT author, title, year, recommend, copy, publ_desc, publ_loc, 
>CEILING(IF(MATCH author AGAINST ( 'Adams') > 0, IF(MATCH title AGAINST 
>('Places') > 0, IF(MATCH publ_desc AGAINST ('Society') > 0, (MATCH author 
>AGAINST ('Adams') + MATCH title AGAINST ('Places') + MATCH publ_desc 
>AGAINST ('Society')), 0), 0), 0)) as x FROM books, publ WHERE publ_id = 
>pub_id ORDER BY x DESC
>
>Hope this helps!
>
>Cheers, Nessi
>
>
>At 22:22 26/06/01 , you wrote:
>>Ok,
>>
>>The message set that I noticed this morning sparked my interest in this.
>>Currently I'm developing a search engine that will utilize mysql's
>>fulltext match technology. The problem that i've run into is, I can't
>>seem to find any documentation on how to force an AND search with in
>>MATCH AGAINST syntax, As far as I can tell this is `OR' only. Has is
>>there
>>an option to force AND? If so could this please be appended to the
>>manual
>>under the `MySQL Full-text Search' section. Thanks.
>>
>>-
>>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

More questions about Fulltext searching.

[Colin Faber]

Ok, 

The message set that I noticed this morning sparked my interest in this. 
Currently I'm developing a search engine that will utilize mysql's 
fulltext match technology. The problem that i've run into is, I can't
seem to find any documentation on how to force an AND search with in
MATCH AGAINST syntax, As far as I can tell this is `OR' only. Has is
there
an option to force AND? If so could this please be appended to the
manual
under the `MySQL Full-text Search' section. Thanks.

-
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

RE: Phrase based fulltext searching

[Braxton Robbason]

I thought I read that was in the plan for 4.0.  I can't find where that's
stated in the docs though - all I see is the stuff about boolean operators.

Does anyone have experience with using the AOL PLS package for text
searching? I am thinking of using it.

Braxton


-Original Message-
From: Philip Mak [mailto:[EMAIL PROTECTED]]
Sent: Wednesday, April 18, 2001 5:39 PM
To: [EMAIL PROTECTED]
Subject: Phrase based fulltext searching


I read through the MySQL documentation on full text indexing, and there
does not seem to be a way to search for a *phrase*, e.g. searching for
a document that contains "Sailor Moon", as opposed to one that contains
the word "Sailor" and the word "Moon", not necessarily together. (Unless I
use LIKE "%Sailor Moon%", but that's inefficient...)

Is the above statement correct?

What techniques do people here use to overcome that limitation? (Using a
program separate from MySQL for indexing text, perhaps?)

-Philip Mak ([EMAIL PROTECTED])


-
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

Phrase based fulltext searching

[Philip Mak]

I read through the MySQL documentation on full text indexing, and there
does not seem to be a way to search for a *phrase*, e.g. searching for
a document that contains "Sailor Moon", as opposed to one that contains
the word "Sailor" and the word "Moon", not necessarily together. (Unless I
use LIKE "%Sailor Moon%", but that's inefficient...)

Is the above statement correct?

What techniques do people here use to overcome that limitation? (Using a
program separate from MySQL for indexing text, perhaps?)

-Philip Mak ([EMAIL PROTECTED])


-
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

Re: Problems with MySQL fulltext searching.

[Sergei Golubchik]

Hi!

> 
> mysql> SELECT *,MATCH a,b AGAINST ('collections support') as x FROM t;
> +--++--+
> | a| b  | x|
> +--++--+
> | MySQL has now support| for full-text search   |0 |
> | Full-text indexes| are called collections | 0.38341854994499 |
> | Function MATCH ... AGAINST() | is used to do a search |0 |
> | mysql has now support| for full-text search   |0 |
> | collections support  | foo| 0.39634908797834 |
> +--++--+
> 5 rows in set (0.00 sec)
> 
> mysql> INSERT INTO t VALUES ('collections',' support');
> Query OK, 1 row affected (0.00 sec)
> mysql> SELECT *,MATCH a,b AGAINST ('collections support') as x FROM t;
> +--++--+
> | a| b  | x|
> +--++--+
> | MySQL has now support| for full-text search   |0 |
> | Full-text indexes| are called collections |0 |
> | Function MATCH ... AGAINST() | is used to do a search |0 |
> | mysql has now support| for full-text search   |0 |
> | collections support  | foo|0 |
> | collections  |  support   |0 |
> +--++--+
> 6 rows in set (0.00 sec)
> 
> 
> Briefly, it seems to us that the x column values should have remained the same,
> or atleast > 0 after our addition of the last entry to the table. It seems
> MySQL detected matches beforehand, but not after.
> 

No, you're wrong.
The relevance in computed from the semantical values of individual words,
and semantical value of the word depends on word frequency in the dataset.
Too frequent words receive semantic value 0 and are effectively ignored.

See manual.

Regards,
Sergei

--
MySQL Development Team
   __  ___ ___   __
  /  |/  /_ __/ __/ __ \/ /   Sergei Golubchik <[EMAIL PROTECTED]>
 / /|_/ / // /\ \/ /_/ / /__  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 <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Problems with MySQL fulltext searching.

[devan . goodwin]

>Description:
The included log

>How-To-Repeat:

>Fix:


>Submitter-Id: Devan Goodwin
>Originator: Devan Goodwin
>Organization: 
National Research Council Canada

>MySQL support: none
>Synopsis:  Problems with MySQL full text search
>Severity:  serious
>Priority:  low
>Category:  mysql
>Class: sw-bug
>Release:   mysql-3.23.32 (Source distribution)

>Environment:

System: Linux flipper.cisti.nrc.ca 2.2.14-6.1.1smp #1 SMP Thu Apr 13 19:55:55 EDT 2000 
i686 unknown
Architecture: i686

Some paths:  /usr/bin/perl /usr/bin/make /usr/bin/gmake /usr/bin/gcc /usr/bin/cc
GCC: Reading specs from /usr/lib/gcc-lib/i386-redhat-linux/egcs-2.91.66/specs
gcc version egcs-2.91.66 19990314/Linux (egcs-1.1.2 release)
Compilation info: CC='gcc'  CFLAGS=''  CXX='c++'  CXXFLAGS=''  LDFLAGS=''
LIBC: 
lrwxrwxrwx1 root root   13 Dec 15 11:49 /lib/libc.so.6 -> libc-2.1.3.so
-rwxr-xr-x1 root root  4101324 Feb 29  2000 /lib/libc-2.1.3.so
-rw-r--r--1 root root 20272704 Feb 29  2000 /usr/lib/libc.a
-rw-r--r--1 root root  178 Feb 29  2000 /usr/lib/libc.so
Configure command: ./configure  --prefix=/home/mysql/mySQL --with-mysqld-user=mysql
Perl: This is perl, version 5.005_03 built for i386-linux




==




We were attempting some simple tests on MySQL fulltext searching, using the
documentation provided on www.mysql.com as a starting point. Our session was
as follows:

mysql> CREATE TABLE t (a VARCHAR(200), b TEXT, FULLTEXT (a,b));
Query OK, 0 rows affected (0.00 sec)


mysql> INSERT INTO t VALUES
-> ('MySQL has now support', 'for full-text search'),
-> ('Full-text indexes', 'are called collections'),
-> ('Function MATCH ... AGAINST()','is used to do a search')
-> ;
Query OK, 3 rows affected (0.03 sec)


mysql> select * from t;
+--++
| a| b  |
+--++
| MySQL has now support| for full-text search   |
| Full-text indexes| are called collections |
| Function MATCH ... AGAINST() | is used to do a search |
+--++
3 rows in set (0.00 sec)

mysql> SELECT * FROM t WHERE MATCH (a,b) AGAINST ('MySQL');
+---+--+
| a | b|
+---+--+
| MySQL has now support | for full-text search |
+---+--+
1 row in set (0.04 sec)

mysql> SELECT MATCH a,b AGAINST ('collections support') as x FROM t;
+--++--+
| a| b  | x|
+--++--+
| MySQL has now support| for full-text search   | 0.65545834044456 |
| Full-text indexes| are called collections | 0.65545834044456 |
| Function MATCH ... AGAINST() | is used to do a search |0 |
+--++--+
3 rows in set (0.00 sec)

mysql> SELECT MATCH a,b AGAINST ('collections support') as x FROM t;
+--+
| x|
+--+
| 0.65545834044456 |
| 0.65545834044456 |
|0 |
+--+
3 rows in set (0.00 sec)

mysql> INSERT INTO t VALUES
-> ('mysql has now support', 'for full-text search');
Query OK, 1 row affected (0.00 sec)


mysql> SELECT *,MATCH a,b AGAINST ('collections support') as x FROM t;
+-+
| x   |
+-+
|   0 |
| 1.0388768903895 |
|   0 |
|   0 |
+-+
4 rows in set (0.00 sec)

mysql> SELECT *,MATCH a,b AGAINST ('collections support') as x FROM t order by x;
+--++-+
| a| b  | x   |
+--++-+
| MySQL has now support| for full-text search   |   0 |
| Full-text indexes| are called collections | 1.0388768903895 |
| Function MATCH ... AGAINST() | is used to do a search |   0 |
| mysql has now support| for full-text search   |   0 |
+--++-+
4 rows in set (0.00 sec)

mysql> SELECT *,MATCH a,b AGAINST ('collections support') as x FROM t order by x, 
descen