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 
http://www.google.com/support/bin/answer.py?answer=427topic=352), 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 
http://www.google.com/search?q=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]

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

[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

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

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

[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

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