Full Text Searching Help

[zackrspv]

MySQL Version:  4.1.22-standard

 

Table layout:

CREATE TABLE `info` (
  `term` varchar(255) NOT NULL default '',
  `def` text,
  FULLTEXT KEY `term` (`term`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

 

MySQL Raw Query: 

SELECT * FROM info WHERE MATCH(term) AGAINST ('RSI')

 

Response:

MySQL returned an empty result set (i.e. zero rows). (Query took 0.0003 sec)

 

Explain Output:

 1  	SIMPLE  	info  	fulltext  	term  	term  	0  	   	1  	Using where

 

# a clear and concise description of what you want this statement to achieve

--Needs to search my terms field for any words, keywords, that the user wishes to search for.

 

# a description of what it's currently doing that's not to your liking

--While it works for normal words, such as 'relative' or '+relative +strength', it doesn't work if you just specify 'RSI'

 

Some table data:

 

 

[Term]                                  [def]

Relative Strength                    A measure of price trend that indicates how a stoc...

Relative Return                      The return that an asset achieves over a period of...

Price-Earnings Relative            A price-earnings ratio of a stock divided by the p...

Relative Strength Index (RSI)    Atechnical momentum indicator that compares the ma...

Relative Vigor Index (RVI)        An indicator used in technical analysis that measu...

Relative Purchase Power Parity  An expansion of the purchase power parity theory s...

 

==== Issue in Human Speak ====

 

We can plainly see, from the above list of data, that 'RSI' exists, ie it exists in the (RSI) form.  However, if we search, using the sql syntax above, for 'RSI' nothing comes back as a result.  Note, most abv's will be in ('s, those they can also be in quotes, or double quotes or brackets, or even separated by -'s.  I can't possibly provide the entire data here as it would just be too huge, but you get the point.  Regardless of how the term appears in the [term] field, each WORD should be search able regardless of punctuation.  How can i accomplish this?

[zackrspv]

So, apparently, I'm stuck.

 

here is what mysql says:

 

The minimum and maximum lengths of words to be indexed are defined by the ft_min_word_len and ft_max_word_len system variables. (See Section 5.1.3, “System Variables”.) The default minimum value is four characters; the default maximum is version dependent. 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

Then you must restart the server and rebuild your FULLTEXT indexes. Note particularly the remarks regarding myisamchk in the instructions following this list.

 

Well, because i'm not able to manage the sql configuration of the server myself, i need an alternate method.

 

I would use LIKE however, but that would do the following:

 

LIKE %'RSI'% would return:  version, revision, and RSI

 

So, how in the world will i get this to work?

[zackrspv]

Anyone?

 

I followed the rules, can y'all please help with this?  I need an alternate option because i'm on a shared host and cannot change the min word limit for full text searches myself, and the host refuses to do it.

 

What else can be done?

[fenway]

In 5.0, you can roll your own text parser and use it for fulltext searching.  Otherwise, it's tricky.