"Scott Purcell" <[EMAIL PROTECTED]> wrote on 05/25/2005 03:35:54 PM:
> 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 weightedaccording > > 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 tryfull-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 > The file they refer to is either my.cnf or my.ini. It's a shared config file used by all of your MySQL tools (you may have had to make changes to itwhile you were setting up your system). Use a text editor (notepad or wordpad, not MS Word) to add the variable/value to the correct section. Sections start when you see a name in square brackets, like [mysql] and go until you see the next name. I recommend a file search to locate it. Even if you used an auto-installer, there should be one somewhere that your server daemon is using. Just load it into a text editor and add the line you need. Save the file then tell the server to reload your settings. Since you are on Win95/98 your only option is to stop then restart your server. Then you will need to drop then recreate your full-text indexes. I know it seems like a lot of steps but this is generally not something people change often. It's set once then left alone for long periods of time. You should only need to go through this once. Shawn Green Database Administrator Unimin Corporation - Spruce Pine
