However, all of these oddities -- over eager stop-list, ignoring short
words, not counting words in more than half the rows -- can be sorted
out by configuration options.  I'm sorry I don't have them to hand,
but half an hour or so on Google should give you the information you
need to make MySQL act like a half-decent text-search engine.

 _/|_	 ___________________________________________________________________
/o ) \/  Mike Taylor    <[log in to unmask]>    http://www.miketaylor.org.uk
)_v__/\  "The Pope claimed he'd been wrong in the past; this was a big
	 surprise" -- Sting, "Jeremiah Blues"



Cloutman, David writes:
 > It seems like there are a number of eccentricities like that. I saw
 > somewhere in the documentation that if a word appears in more than half
 > the rows, it isn't searched. Because of that, I'm only using MATCH to
 > generate relevancy numbers. I'm doing boolean in the search terms. My
 > queries are like:
 > 
 > SELECT `path`, `mdate` FROM `rawtext` WHERE `rawText` LIKE '%book%' AND
 > `rawText` LIKE '%lists%' ORDER BY MATCH (`rawText`) AGAINST ('book
 > lists') DESC, `mdate` DESC
 > 
 > (Yes, I have a table named rawtext with a column named rawText. Shoot
 > me.)
 > 
 > It's far from perfect, but I just need to index a few hundred documents,
 > and it's for staff consumption. I would be curious to know how Postgres
 > compares to MySQL in this area. I'm looking towards finding a long-term
 > alternative to MySQL due to the Sun / Oracle merger, which I don't think
 > will end well for MySQL.
 > 
 > - David
 > 
 > ---
 > David Cloutman <[log in to unmask]>
 > Electronic Services Librarian
 > Marin County Free Library 
 > 
 > -----Original Message-----
 > From: Code for Libraries [mailto:[log in to unmask]] On Behalf Of
 > Genny Engel
 > Sent: Friday, May 29, 2009 12:41 PM
 > To: [log in to unmask]
 > Subject: Re: [CODE4LIB] MySQL Stop Words
 > 
 > 
 > Once I read a study where the document collection to be indexed was in a
 > narrow technical field, and the goal was to present a search that
 > quickly isolated ONLY the most relevant documents.  To this end, they
 > stopworded everything that didn't sufficiently distinguish one document
 > from another.   Their stopword list comprised some 30,000 terms!
 >  
 > If your goal, on the other hand, is to maximize recall at some expense
 > of precision, beware of MySQL full-text MATCH because it dynamically
 > computes new stopwords.  Note this little side note in section 11.8.1 of
 > the manual:
 >  
 > For very small tables, word distribution does not adequately reflect
 > their semantic value, and this model may sometimes produce bizarre
 > results. For example, although the word "MySQL" is present in every row
 > of the articles table shown earlier, a search for the word produces no
 > results [ ... ] The search result is empty because the word "MySQL" is
 > present in at least 50% of the rows. As such, it is effectively treated
 > as a stopword. For large data sets, this is the most desirable behavior:
 > A natural language query should not return every second row from a 1GB
 > table. For small data sets, it may be less desirable. 
 >  
 >  
 >  
 >  
 > Genny Engel
 > Sonoma County Library
 > [log in to unmask]
 > 707 545-0831 x581
 > www.sonomalibrary.org
 >  
 > 
 > 
 > >>> [log in to unmask] 05/29/09 11:26AM >>>
 > In building a search function for some of our internal documents in PHP
 > / MySQL, I took a look at the default list of MySQL English language
 > stop words used in the natural language searching feature. The list is
 > actually quite extensive, and goes well beyond the typical list of "to
 > be" cognates, common prepositions, conjunctions, etc. It also includes a
 > large number of keywords that librarians or academic users might want to
 > search for. Here are a few examples:
 > 
 > available
 > appropriate
 > course
 > follow
 > former
 > novel
 > 
 > There are quite a number of other stop words that I think are suspect.
 > The full list of stop words is located here:
 > http://dev.mysql.com/doc/refman/5.1/en/fulltext-stopwords.html 
 > 
 > I guess the point is that if you're building a library application that
 > takes advantage of MySQL's fulltext searching features, you might want
 > to customize you stop words list on your MySQL installation if you think
 > your library users might want to search the word "novel".
 > 
 > - David
 > 
 > ---
 > David Cloutman <[log in to unmask]>
 > Electronic Services Librarian
 > Marin County Free Library 
 > 
 > Email Disclaimer: http://www.co.marin.ca.us/nav/misc/EmailDisclaimer.cfm