Thanks Sean fo the info. I see where it states the server is configured for 4 character indexing. I would like to try and set it to 3 and do not understand what an options file is: The documentation states the following:
* The minimum and maximum length of words to be indexed is defined by the ft_min_word_len and ft_max_word_len system variables (available as of MySQL 4.0.0). See <http://dev.mysql.com/doc/mysql/en/server-system-variables.html> Section 5.3.3, "Server System Variables". The default minimum value is four characters. The default maximum depends on your version of MySQL. If you change either value, you must rebuild your FULLTEXT indexes. For example, if you want three-character words to be searchable, you can set the ft_min_word_len variable by putting the following lines in an option file: [mysqld] ft_min_word_len=3 I use mysql from a binary install, and I am just learning it. How do I create this file, and where does it go? Thanks, Scott -----Original Message----- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Wednesday, May 25, 2005 2:21 PM To: Brian Mansell Cc: mysql@lists.mysql.com; Scott Purcell Subject: Re: Fulltext Simple Question Brian Mansell <[EMAIL PROTECTED]> wrote on 05/25/2005 03:09:03 PM: > Scott - > > Check this excerpt out ( > http://dev.mysql.com/doc/mysql/en/fulltext-search.html ) from the MySQL > Documentation. I hope it helps! > > --bemansell > > ... > > "Every correct word in the collection and in the query is weighted according > to its significance in the collection or query. This way, a word that is > present in many documents has a lower weight (and may even have a zero > weight), because it has lower semantic value in this particular collection. > Conversely, if the word is rare, it receives a higher weight. The weights of > the words are then combined to compute the relevance of the row. > > Such a technique works best with large collections (in fact, it was > carefully tuned this way). 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, a search for the word produces no > results: > > mysql> SELECT * FROM articles > -> WHERE MATCH (title,body) AGAINST ('MySQL'); > Empty set (0.00 sec) > > 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 datasets, this is the most desirable behavior---a natural language > query should not return every second row from a 1GB table. For small > datasets, it may be less desirable. > > A word that matches half of rows in a table is less likely to locate > relevant documents. In fact, it most likely finds plenty of irrelevant > documents. We all know this happens far too often when we are trying to find > something on the Internet with a search engine. It is with this reasoning > that rows containing the word are assigned a low semantic value for *the > particular dataset in which they occur*. A given word may exceed the 50% > threshold in one dataset but not another. > > The 50% threshold has a significant implication when you first try full-text > searching to see how it works: If you create a table and insert only one or > two rows of text into it, every word in the text occurs in at least 50% of > the rows. As a result, no search returns any results. Be sure to insert at > least three rows, and preferably many more." > > > > On 5/25/05, Scott Purcell <[EMAIL PROTECTED]> wrote: > > > > Hello, > > I am running 4.0.15 for Win95/98 and am working through the docs. > > > > I created a "text" type field with a 'fulltext' index. As I am > > experimenting, I have run into a couple of questions: > > > > First off, I was having trouble getting results. So I added the word > > "foobar" to one of the descriptions: > > and that worked with this query: > > select * from item where match(name, description) against('foobar') > > > > > > > > I have a word 'red' that appears 5-10 times, in a tmp table of 60 records. > > If I run that query with 'red' > > select * from item where match(name, description) against('red'); > > it returns empty set > > > > Upon reading, it looks like it is really trying to only get "unique" names > > from the index. But in my case the 'red' is a description that I would like > > to get back. Anyway to force this to return results? > > > > Any info would be helpful. I have read, but it gets a little confusing > > first time through. > > > > Thanks, > > Scott > > The other thing to remember is the "minimum word length". By default it is set to 4. RED has only 3 characters so it would not have been indexed. That would explain why FT searches for RED is not returning any records. See here for FT tuning (settings): http://dev.mysql.com/doc/mysql/en/fulltext-fine-tuning.html
