Re: Please help me: Boolean fulltext searches, AND instead of OR

[Michael Stassen]

John thegimper wrote:

This is what i need:

Posted by gogman on Monday May 5 2003, @10:42am on the mysql website:

MySQL defaults to an 'OR'. Example: 'dog cat' = 'dog OR cat'. Most fulltext 
search engines default to an 'AND'. These include: AltaVista, Fast Search, 
Google, Hotbot, Lycos, Northern Light and Yahoo. Excite is an exception that 
defaults to an 'OR'. 


It's not clear to me that this is entirely accurate (Google, for example, is 
a relevance search 
), but I 
don't think web search engine front ends are particularly relevant in any case.



New Feature: set-variable = ft_boolean_default='AND'
vs 'OR'

('OR' would be the default setting so as to not break older code)

With a 'AND' default 'OR' would have to be explicit. Example: 'dog cat' = 'dog 
AND cat', 'dog OR cat' - requires 'OR' to be set.


Performance tests are indicating a 5-7 times increase in search speed 
with "AND" vs "OR" statements.


--

I have done some searches on google and found several people wanting to do the 
same... but no solutions.
 
Is there still no solution for this? Every large searchengine works like

this.


Why do you *need* this?  More to the point, why should mysql do this?

Honestly, I think you are confusing the user interface, the application, and 
the back end.  Your user interface is free to include a search box where the 
user can type 'dog cat' with the expectation that only documents containing 
both (AND) will be returned.  Your application needs to parse the request 
and send the proper query to mysql (the back end).  Mysql is just a useful 
tool.  It stores your data and provides various forms of full-text searching:


Relevance scoring -

  WHERE MATCH (doc, description) AGAINST ('dog cat')

OR searches -

  WHERE MATCH (doc, description) AGAINST ('dog cat' IN BOOLEAN MODE)

AND searches -

  WHERE MATCH (doc, description) AGAINST ('+dog +cat' IN BOOLEAN MODE)

Given these choices, I'm not sure what difference it makes what the default 
is.  You surely don't propose to pass unmodified user input to mysql, as 
that's not a good idea (see SQL injection 
).  If you want AND searches, 
simply have your app add the + signs to the user input as it builds the 
query to send to mysql.


If you are determined to change mysql's default behavior, then Sergei has 
already given a solution earlier in this thread: swap the '+' and ' ' in the 
ft_boolean_syntax variable.  The only objection raised to this was the 
suggestion that if the user prepends a '+' to a word, it becomes optional 
(OR instead of AND), but that's a moot point, as your app will, of course, 
strip the '+' when parsing the user's input.


Michael


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

Re: Please help me: Boolean fulltext searches, AND instead of OR

[John thegimper]

This is what i need:

Posted by gogman on Monday May 5 2003, @10:42am on the mysql website:

MySQL defaults to an 'OR'. Example: 'dog cat' = 'dog OR cat'. Most fulltext 
search engines default to an 'AND'. These include: AltaVista, Fast Search, 
Google, Hotbot, Lycos, Northern Light and Yahoo. Excite is an exception that 
defaults to an 'OR'. 


New Feature: set-variable = ft_boolean_default='AND'

vs 'OR'

('OR' would be the default setting so as to not break older code)

With a 'AND' default 'OR' would have to be explicit. Example: 'dog cat' = 'dog 
AND cat', 'dog OR cat' - requires 'OR' to be set.

Performance tests are indicating a 5-7 times increase in search speed 
with "AND" vs "OR" statements.

--


I have done some searches on google and found several people wanting to do the 
same... but no solutions.


Quoting John thegimper <[EMAIL PROTECTED]>:

> Is there still no solution for this? Every large searchengine works like
> this.
> 
> Quoting:
> > 
> > 
> > Hi that works fine only now if a user puts + in front of a word that
> > word 
> > becomes optional = OR?
> > What i would like is the search to work exactly like before only that
> it
> > 
> > defaults to AND instead of OR.
> > Almost every search engine i have tried, google etc. works like this.
> > "black horse cat dog" only show results with all words present.
> > 
> > This is what i did, set global ft_boolean_syntax = ' +-><()~*:""&|'
> > 
> > I really appreciate your help!!
> > 
> > >From: Sergei Golubchik <[EMAIL PROTECTED]>
> > >To: Jessica Svensson <[EMAIL PROTECTED]>
> > >CC: [EMAIL PROTECTED]
> > >Subject: Re: Please help me: Boolean fulltext searches, AND instead
> of
> > OR
> > >Date: Wed, 23 Mar 2005 22:32:49 +0100
> > >
> > >Hi!
> > >
> > >On Mar 23, Jessica Svensson wrote:
> > > > Is there any way i can get results with AND instead of OR?
> > > > Trying to search for "black cat" should only return records that 
> > >contains
> > > > both black and cat.
> > > >
> > > > I'm using the following code to get my result:
> > > >
> > > > SELECT * FROM `searchtbl` WHERE MATCH (text) AGAINST ('black cat'
> IN
> > > > BOOLEAN MODE);
> > > >
> > > > sure there must be an easy way to change the default word
> separator
> > to 
> > >AND
> > > > instead of OR?
> > >
> > >There is. See ft_boolean_syntax variable - it defines what characters
> > is
> > >used for each operator. In particular it defines '+' for "must be
> > >present" and a space ' ' for "optionally present" words.
> > >
> > >You want to put the space first (for "must be present" words)
> > >
> > >Regards,
> > >Sergei
> > >
> > >--
> > >__  ___ ___   __
> > >   /  |/  /_ __/ __/ __ \/ /   Sergei Golubchik <[EMAIL PROTECTED]>
> > >  / /|_/ / // /\ \/ /_/ / /__  MySQL AB, Senior Software Developer
> > >/_/  /_/\_, /___/\___\_\___/  Osnabrueck, Germany
> > ><___/  www.mysql.com
> > 
> > _
> > Lättare att hitta drömresan med MSN Resor http://www.msn.se/resor/
> > 
> > 
> 
> 
>  
> 
> -
> FREE E-MAIL IN 1 MINUTE!
>  - [EMAIL PROTECTED] - http://www.pc.nu
> 
> -- 
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
> 
> 



 

-
FREE E-MAIL IN 1 MINUTE!
 - [EMAIL PROTECTED] - http://www.pc.nu

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

Re: Please help me: Boolean fulltext searches, AND instead of OR

[John thegimper]

Is there still no solution for this? Every large searchengine works like this.

Quoting:
> 
> 
> Hi that works fine only now if a user puts + in front of a word that
> word 
> becomes optional = OR?
> What i would like is the search to work exactly like before only that it
> 
> defaults to AND instead of OR.
> Almost every search engine i have tried, google etc. works like this.
> "black horse cat dog" only show results with all words present.
> 
> This is what i did, set global ft_boolean_syntax = ' +-><()~*:""&|'
> 
> I really appreciate your help!!
> 
> >From: Sergei Golubchik <[EMAIL PROTECTED]>
> >To: Jessica Svensson <[EMAIL PROTECTED]>
> >CC: [EMAIL PROTECTED]
> >Subject: Re: Please help me: Boolean fulltext searches, AND instead of
> OR
> >Date: Wed, 23 Mar 2005 22:32:49 +0100
> >
> >Hi!
> >
> >On Mar 23, Jessica Svensson wrote:
> > > Is there any way i can get results with AND instead of OR?
> > > Trying to search for "black cat" should only return records that 
> >contains
> > > both black and cat.
> > >
> > > I'm using the following code to get my result:
> > >
> > > SELECT * FROM `searchtbl` WHERE MATCH (text) AGAINST ('black cat' IN
> > > BOOLEAN MODE);
> > >
> > > sure there must be an easy way to change the default word separator
> to 
> >AND
> > > instead of OR?
> >
> >There is. See ft_boolean_syntax variable - it defines what characters
> is
> >used for each operator. In particular it defines '+' for "must be
> >present" and a space ' ' for "optionally present" words.
> >
> >You want to put the space first (for "must be present" words)
> >
> >Regards,
> >Sergei
> >
> >--
> >__  ___ ___   __
> >   /  |/  /_ __/ __/ __ \/ /   Sergei Golubchik <[EMAIL PROTECTED]>
> >  / /|_/ / // /\ \/ /_/ / /__  MySQL AB, Senior Software Developer
> >/_/  /_/\_, /___/\___\_\___/  Osnabrueck, Germany
> ><___/  www.mysql.com
> 
> _
> Lättare att hitta drömresan med MSN Resor http://www.msn.se/resor/
> 
> 


 

-
FREE E-MAIL IN 1 MINUTE!
 - [EMAIL PROTECTED] - http://www.pc.nu

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

Re: Please help me: Boolean fulltext searches, AND instead of OR

[leegold]

On Wed, 23 Mar 2005 22:22:34 +, "Jessica Svensson"
<[EMAIL PROTECTED]> said:
> Hi that works fine only now if a user puts + in front of a word that word 
> becomes optional = OR?
> What i would like is the search to work exactly like before only that it 
> defaults to AND instead of OR.
> Almost every search engine i have tried, google etc. works like this.
> "black horse cat dog" only show results with all words present.

I'm a little late in the discussion but...
Are you using a scripting language with MYSQL? It'd be easy w/PHP


> 
> This is what i did, set global ft_boolean_syntax = ' +-><()~*:""&|'
> 
> I really appreciate your help!!
> 
> >From: Sergei Golubchik <[EMAIL PROTECTED]>
> >To: Jessica Svensson <[EMAIL PROTECTED]>
> >CC: mysql@lists.mysql.com
> >Subject: Re: Please help me: Boolean fulltext searches, AND instead of OR
> >Date: Wed, 23 Mar 2005 22:32:49 +0100
> >
> >Hi!
> >
> >On Mar 23, Jessica Svensson wrote:
> > > Is there any way i can get results with AND instead of OR?
> > > Trying to search for "black cat" should only return records that 
> >contains
> > > both black and cat.
> > >
> > > I'm using the following code to get my result:
> > >
> > > SELECT * FROM `searchtbl` WHERE MATCH (text) AGAINST ('black cat' IN
> > > BOOLEAN MODE);
> > >
> > > sure there must be an easy way to change the default word separator to 
> >AND
> > > instead of OR?
> >
> >There is. See ft_boolean_syntax variable - it defines what characters is
> >used for each operator. In particular it defines '+' for "must be
> >present" and a space ' ' for "optionally present" words.
> >
> >You want to put the space first (for "must be present" words)
> >
> >Regards,
> >Sergei
> >
> >--
> >__  ___ ___   __
> >   /  |/  /_ __/ __/ __ \/ /   Sergei Golubchik <[EMAIL PROTECTED]>
> >  / /|_/ / // /\ \/ /_/ / /__  MySQL AB, Senior Software Developer
> >/_/  /_/\_, /___/\___\_\___/  Osnabrueck, Germany
> ><___/  www.mysql.com
> 
> _
> Lättare att hitta drömresan med MSN Resor http://www.msn.se/resor/
> 
> 
> -- 
> 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: Please help me: Boolean fulltext searches, AND instead of OR

[Jessica Svensson]

Hi that works fine only now if a user puts + in front of a word that word 
becomes optional = OR?
What i would like is the search to work exactly like before only that it 
defaults to AND instead of OR.
Almost every search engine i have tried, google etc. works like this.
"black horse cat dog" only show results with all words present.

This is what i did, set global ft_boolean_syntax = ' +-><()~*:""&|'
I really appreciate your help!!
From: Sergei Golubchik <[EMAIL PROTECTED]>
To: Jessica Svensson <[EMAIL PROTECTED]>
CC: mysql@lists.mysql.com
Subject: Re: Please help me: Boolean fulltext searches, AND instead of OR
Date: Wed, 23 Mar 2005 22:32:49 +0100
Hi!
On Mar 23, Jessica Svensson wrote:
> Is there any way i can get results with AND instead of OR?
> Trying to search for "black cat" should only return records that 
contains
> both black and cat.
>
> I'm using the following code to get my result:
>
> SELECT * FROM `searchtbl` WHERE MATCH (text) AGAINST ('black cat' IN
> BOOLEAN MODE);
>
> sure there must be an easy way to change the default word separator to 
AND
> instead of OR?

There is. See ft_boolean_syntax variable - it defines what characters is
used for each operator. In particular it defines '+' for "must be
present" and a space ' ' for "optionally present" words.
You want to put the space first (for "must be present" words)
Regards,
Sergei
--
   __  ___ ___   __
  /  |/  /_ __/ __/ __ \/ /   Sergei Golubchik <[EMAIL PROTECTED]>
 / /|_/ / // /\ \/ /_/ / /__  MySQL AB, Senior Software Developer
/_/  /_/\_, /___/\___\_\___/  Osnabrueck, Germany
   <___/  www.mysql.com
_
Lättare att hitta drömresan med MSN Resor http://www.msn.se/resor/
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Please help me: Boolean fulltext searches, AND instead of OR

[Sergei Golubchik]

Hi!

On Mar 23, Jessica Svensson wrote:
> Is there any way i can get results with AND instead of OR?
> Trying to search for "black cat" should only return records that contains 
> both black and cat.
> 
> I'm using the following code to get my result:
> 
> SELECT * FROM `searchtbl` WHERE MATCH (text) AGAINST ('black cat' IN 
> BOOLEAN MODE);
> 
> sure there must be an easy way to change the default word separator to AND 
> instead of OR?

There is. See ft_boolean_syntax variable - it defines what characters is
used for each operator. In particular it defines '+' for "must be
present" and a space ' ' for "optionally present" words.

You want to put the space first (for "must be present" words)
 
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: Please help me: Boolean fulltext searches, AND instead of OR

[Brent Baisley]

I couldn't find much on the variable you were trying to change. 
Although it may have changed and now be called ft_boolean_syntax. But 
that still won't help you since it doesn't appear to have an option to 
change the default separator.
I think you will need to add the + to each word if you want the AND 
behavior. Why are you so averse to parsing? It would make it easier for 
the user to input search terms.

If it's helpful, below is some php code I use for parsing search 
phrases entered by users. It adds + and * based on quotes or 
independent words. I wrote it a long time ago and it problem needs to 
be looked at to make it better, but it's worked for me for a while now. 
It will give you the AND behavior you are looking for.

function prepFullTextSearch($searchVal) {
	//Split words into list
	$word_List			= explode(' ',trim($searchVal));
	//Step through word list to get search phrases
	$i	= 0;
	$isPhrase= false;
	foreach($word_List as $word) {
		$searchItems[$i]	= trim( ($isPhrase?$searchItems[$i].' '.$word:$word) 
);
		//Check for start of Phrase
		if(substr($searchItems[$i],0,1) == '"') {
			$isPhrase		= true;
		}
		//If not building a phrase, append wildcard (*) to end of word
		if(!$isPhrase) {
			$searchItems[$i]	.= '*';
			$i++;
		}
		//Check for end of Phrase
		if(substr($searchItems[$i],-1) == '"') {
			$isPhrase		= false;
			$i++;
		}
	}
	$searchVal= '+'.implode(' +',$searchItems);
	return $searchVal;
}

On Mar 23, 2005, at 2:45 PM, Jessica Svensson wrote:
Is there any way i can get results with AND instead of OR?
Trying to search for "black cat" should only return records that 
contains both black and cat.

I'm using the following code to get my result:
SELECT * FROM `searchtbl` WHERE MATCH (text) AGAINST ('black cat' IN 
BOOLEAN MODE);

sure there must be an easy way to change the default word separator to 
AND instead of OR?

I found this:
set-variable = ft_boolean_default='AND'
SET ft_boolean_default = 'AND'
But it does not work, everything would be sooo much easier if this was 
possible.

"black cat" is only an example and the real query comes from user 
input. So it can be anything like +cat -dog +"big nose" -horse white 
black -red
so parsing the input is not what i want, i just want to change the 
default word separator to AND instead of OR.

Now i´m running 4.1.10 and also tried with 4.0.24
Please help me, there must be a way to change this?!?!
_
Hitta rätt på nätet med MSN Sök http://search.msn.se/
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:
http://lists.mysql.com/[EMAIL PROTECTED]


--
Brent Baisley
Systems Architect
Landover Associates, Inc.
Search & Advisory Services for Advanced Technology Environments
p: 212.759.6400/800.759.0577
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Please help me: Boolean fulltext searches, AND instead of OR

[Jessica Svensson]

Is there any way i can get results with AND instead of OR?
Trying to search for "black cat" should only return records that contains 
both black and cat.

I'm using the following code to get my result:
SELECT * FROM `searchtbl` WHERE MATCH (text) AGAINST ('black cat' IN BOOLEAN 
MODE);

sure there must be an easy way to change the default word separator to AND 
instead of OR?

I found this:
set-variable = ft_boolean_default='AND'
SET ft_boolean_default = 'AND'
But it does not work, everything would be sooo much easier if this was 
possible.

I know that i could put a + infront of every word but thats not what i want 
since the query comes from a searchbox and i dont want to write som code to 
manipulate the indata so ite shows correctly i just want to change the 
default word separator from OR to AND.

Maybe it´s ft_boolean_syntax that should be changed? If so to what... please 
help me before i try to strangle myself :P

_
Chatt: Träffa nya nätkompisar på Habbo Hotel 
http://habbohotel.msn.se/habbo/sv/channelizer

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

Please help me: Boolean fulltext searches, AND instead of OR

[Jessica Svensson]

Is there any way i can get results with AND instead of OR?
Trying to search for "black cat" should only return records that contains 
both black and cat.

I'm using the following code to get my result:
SELECT * FROM `searchtbl` WHERE MATCH (text) AGAINST ('black cat' IN BOOLEAN 
MODE);

sure there must be an easy way to change the default word separator to AND 
instead of OR?

I found this:
set-variable = ft_boolean_default='AND'
SET ft_boolean_default = 'AND'
But it does not work, everything would be sooo much easier if this was 
possible.

"black cat" is only an example and the real query comes from user input. So 
it can be anything like +cat -dog +"big nose" -horse white black -red
so parsing the input is not what i want, i just want to change the default 
word separator to AND instead of OR.

Now i´m running 4.1.10 and also tried with 4.0.24
Please help me, there must be a way to change this?!?!
_
Hitta rätt på nätet med MSN Sök http://search.msn.se/
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]