Full Text Searching Help

So, I understand how to do it, and it works for the most part; for
example if my code is:

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

I get around 5 results, such as the following:

Relative Strength
Relative Return
Price-Earnings Relative
Relative Strength Index (RSI)
Relative Vigor Index (RVI)
Relative Purchase Power Parity

But, let us assume that we are searching for 'RSI':

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

When we do that, boom, no results; yet we can clearly SEE that RSI
does exist in the terms above.

How can i get it to return the result?

On Mar 7, 12:15 pm, zackrspv <old...@gmail.com> wrote:
> So, I understand how to do it, and it works for the most part; for
> example if my code is:
>
> SELECT * FROM info WHERE MATCH(term) AGAINST ('relative')
>
> I get around 5 results, such as the following:
>
> Relative Strength
> Relative Return
> Price-Earnings Relative
> Relative Strength Index (RSI)
> Relative Vigor Index (RVI)
> Relative Purchase Power Parity
>
> But, let us assume that we are searching for 'RSI':
>
> SELECT * FROM info WHERE MATCH(term) AGAINST ('RSI')
>
> When we do that, boom, no results; yet we can clearly SEE that RSI
> does exist in the terms above.
>
> How can i get it to return the result?

Note, also, that I can use the following code as well and it works
fine:

SELECT * FROM info WHERE MATCH(term) AGAINST ('+RSI' IN BOOLEAN MODE)

But if i try and specify RSI in there:

Still, no results.

So, maybe i'm just confused on how to do it, or i just don't get it :/

>
> SELECT * FROM info WHERE MATCH(term) AGAINST ('+RSI' IN BOOLEAN MODE)
>

Ooops, meant:

SELECT * FROM info WHERE MATCH(term) AGAINST ('+relative +strength' IN
BOOLEAN MODE)

haha, of course i meant to say that the quote text above DIDN'T work
lol sorry, i'm a bit tired i guess.

On Mar 7, 3:22*pm, zackrspv <old...@gmail.com> wrote:
> > SELECT * FROM info WHERE MATCH(term) AGAINST ('+RSI' IN BOOLEAN MODE)
>
> Ooops, meant:
>
> SELECT * FROM info WHERE MATCH(term) AGAINST ('+relative +strength' IN
> BOOLEAN MODE)
>
> haha, of course i meant to say that the quote text above DIDN'T work
> lol sorry, i'm a bit tired i guess.

Two possible things to start. I don't remember how MySQL handles the
'(', but it is possible that it is looking for the 'word' 'RSI' and is
not matching it because it finds the 'word' '(RSI)'

The other, more likely explaination is that the minimum word length is
set to 4 characters, so it's just ignoring RSI.

Check out this link

http://dev.mysql.com/doc/refman/5.0/...ne-tuning.html

and look for 'ft_min_word_len'

zackrspv wrote:
> So, I understand how to do it, and it works for the most part; for
> example if my code is:
>
> SELECT * FROM info WHERE MATCH(term) AGAINST ('relative')
>
> I get around 5 results, such as the following:
>
> Relative Strength
> Relative Return
> Price-Earnings Relative
> Relative Strength Index (RSI)
> Relative Vigor Index (RVI)
> Relative Purchase Power Parity
>
> But, let us assume that we are searching for 'RSI':
>
> SELECT * FROM info WHERE MATCH(term) AGAINST ('RSI')
>
> When we do that, boom, no results; yet we can clearly SEE that RSI
> does exist in the terms above.
>
> How can i get it to return the result?
>

From the MySQL manual under full text searches:

"Any word that is too short is ignored. The default minimum length of
words that are found by full-text searches is four characters"

RSI is, of course, shorter than that.

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex@attglobal.net
==================

On Mar 7, 2:09 pm, Jerry Stuckle <jstuck...@attglobal.net> wrote:
> zackrspv wrote:
> > So, I understand how to do it, and it works for the most part; for
> > example if my code is:
>
> > SELECT * FROM info WHERE MATCH(term) AGAINST ('relative')
>
> > I get around 5 results, such as the following:
>
> > Relative Strength
> > Relative Return
> > Price-Earnings Relative
> > Relative Strength Index (RSI)
> > Relative Vigor Index (RVI)
> > Relative Purchase Power Parity
>
> > But, let us assume that we are searching for 'RSI':
>
> > SELECT * FROM info WHERE MATCH(term) AGAINST ('RSI')
>
> > When we do that, boom, no results; yet we can clearly SEE that RSI
> > does exist in the terms above.
>
> > How can i get it to return the result?
>
> From the MySQL manual under full text searches:
>
> "Any word that is too short is ignored. The default minimum length of
> words that are found by full-text searches is four characters"
>
> RSI is, of course, shorter than that.
>
> --
> ==================
> Remove the "x" from my email address
> Jerry Stuckle
> JDS Computer Training Corp.
> jstuck...@attglobal.net
> ==================

now see that just sucks; how can i get it to display the results if my
clients are searching for things like RSI, RVI, ESS, etc. Most of the
don't know what they mean, and the terms would still have to display
that. If i can't use a full text search, i'd use %like% but that'd be
pointless as it would return other things like 'RSI' for 'version' and
'conversion'.

Any ideas on a way to get around this?

zackrspv wrote:
> On Mar 7, 2:09 pm, Jerry Stuckle <jstuck...@attglobal.net> wrote:
>> zackrspv wrote:
>>> So, I understand how to do it, and it works for the most part; for
>>> example if my code is:
>>> SELECT * FROM info WHERE MATCH(term) AGAINST ('relative')
>>> I get around 5 results, such as the following:
>>> Relative Strength
>>> Relative Return
>>> Price-Earnings Relative
>>> Relative Strength Index (RSI)
>>> Relative Vigor Index (RVI)
>>> Relative Purchase Power Parity
>>> But, let us assume that we are searching for 'RSI':
>>> SELECT * FROM info WHERE MATCH(term) AGAINST ('RSI')
>>> When we do that, boom, no results; yet we can clearly SEE that RSI
>>> does exist in the terms above.
>>> How can i get it to return the result?
>> From the MySQL manual under full text searches:
>>
>> "Any word that is too short is ignored. The default minimum length of
>> words that are found by full-text searches is four characters"
>>
>> RSI is, of course, shorter than that.
>>
>> --
>> ==================
>> Remove the "x" from my email address
>> Jerry Stuckle
>> JDS Computer Training Corp.
>> jstuck...@attglobal.net
>> ==================
>
> now see that just sucks; how can i get it to display the results if my
> clients are searching for things like RSI, RVI, ESS, etc. Most of the
> don't know what they mean, and the terms would still have to display
> that. If i can't use a full text search, i'd use %like% but that'd be
> pointless as it would return other things like 'RSI' for 'version' and
> 'conversion'.
>
> Any ideas on a way to get around this?
>

Check the docs. That's the default; you can change it. I don't
remember the exact parm offhand - never had to change it, myself.

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex@attglobal.net
==================

On Mar 7, 5:39 pm, Jerry Stuckle <jstuck...@attglobal.net> wrote:
> zackrspv wrote:
> > On Mar 7, 2:09 pm, Jerry Stuckle <jstuck...@attglobal.net> wrote:
> >> zackrspv wrote:
> >>> So, I understand how to do it, and it works for the most part; for
> >>> example if my code is:
> >>> SELECT * FROM info WHERE MATCH(term) AGAINST ('relative')
> >>> I get around 5 results, such as the following:
> >>> Relative Strength
> >>> Relative Return
> >>> Price-Earnings Relative
> >>> Relative Strength Index (RSI)
> >>> Relative Vigor Index (RVI)
> >>> Relative Purchase Power Parity
> >>> But, let us assume that we are searching for 'RSI':
> >>> SELECT * FROM info WHERE MATCH(term) AGAINST ('RSI')
> >>> When we do that, boom, no results; yet we can clearly SEE that RSI
> >>> does exist in the terms above.
> >>> How can i get it to return the result?
> >> From the MySQL manual under full text searches:
>
> >> "Any word that is too short is ignored. The default minimum length of
> >> words that are found by full-text searches is four characters"
>
> >> RSI is, of course, shorter than that.
>
> >> --
> >> ==================
> >> Remove the "x" from my email address
> >> Jerry Stuckle
> >> JDS Computer Training Corp.
> >> jstuck...@attglobal.net
> >> ==================
>
> > now see that just sucks; how can i get it to display the results if my
> > clients are searching for things like RSI, RVI, ESS, etc. Most of the
> > don't know what they mean, and the terms would still have to display
> > that. If i can't use a full text search, i'd use %like% but that'd be
> > pointless as it would return other things like 'RSI' for 'version' and
> > 'conversion'.
>
> > Any ideas on a way to get around this?
>
> Check the docs. That's the default; you can change it. I don't
> remember the exact parm offhand - never had to change it, myself.
>
> --
> ==================
> Remove the "x" from my email address
> Jerry Stuckle
> JDS Computer Training Corp.
> jstuck...@attglobal.net
> ==================

Well, I can't change it on my host as i'm on a shared account. The
host refuses to change it as well, they consider it a security risk,
tho I do not see how. There has to be an alternate way to do this,
such as using regular expressions, etc, but i'm not sure; how can I
accomplish what I need?

zackrspv wrote:
> On Mar 7, 5:39 pm, Jerry Stuckle <jstuck...@attglobal.net> wrote:
>> zackrspv wrote:
>>> On Mar 7, 2:09 pm, Jerry Stuckle <jstuck...@attglobal.net> wrote:
>>>> zackrspv wrote:
>>>>> So, I understand how to do it, and it works for the most part; for
>>>>> example if my code is:
>>>>> SELECT * FROM info WHERE MATCH(term) AGAINST ('relative')
>>>>> I get around 5 results, such as the following:
>>>>> Relative Strength
>>>>> Relative Return
>>>>> Price-Earnings Relative
>>>>> Relative Strength Index (RSI)
>>>>> Relative Vigor Index (RVI)
>>>>> Relative Purchase Power Parity
>>>>> But, let us assume that we are searching for 'RSI':
>>>>> SELECT * FROM info WHERE MATCH(term) AGAINST ('RSI')
>>>>> When we do that, boom, no results; yet we can clearly SEE that RSI
>>>>> does exist in the terms above.
>>>>> How can i get it to return the result?
>>>> From the MySQL manual under full text searches:
>>>> "Any word that is too short is ignored. The default minimum length of
>>>> words that are found by full-text searches is four characters"
>>>> RSI is, of course, shorter than that.
>>>> --
>>>> ==================
>>>> Remove the "x" from my email address
>>>> Jerry Stuckle
>>>> JDS Computer Training Corp.
>>>> jstuck...@attglobal.net
>>>> ==================
>>> now see that just sucks; how can i get it to display the results if my
>>> clients are searching for things like RSI, RVI, ESS, etc. Most of the
>>> don't know what they mean, and the terms would still have to display
>>> that. If i can't use a full text search, i'd use %like% but that'd be
>>> pointless as it would return other things like 'RSI' for 'version' and
>>> 'conversion'.
>>> Any ideas on a way to get around this?
>> Check the docs. That's the default; you can change it. I don't
>> remember the exact parm offhand - never had to change it, myself.
>>
>> --
>> ==================
>> Remove the "x" from my email address
>> Jerry Stuckle
>> JDS Computer Training Corp.
>> jstuck...@attglobal.net
>> ==================
>
> Well, I can't change it on my host as i'm on a shared account. The
> host refuses to change it as well, they consider it a security risk,
> tho I do not see how. There has to be an alternate way to do this,
> such as using regular expressions, etc, but i'm not sure; how can I
> accomplish what I need?
>

Not easily, I'm afraid. You might be able to do it with a regex, but
I'm not good at regex's.

And I don't really blame your hosting company - it wouldn't be a
security risk, but searching on short words can load down the server.

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex@attglobal.net
==================

On Mar 8, 1:01*pm, Jerry Stuckle <jstuck...@attglobal.net> wrote:
> zackrspv wrote:
> > On Mar 7, 5:39 pm, Jerry Stuckle <jstuck...@attglobal.net> wrote:
> >> zackrspv wrote:
> >>> On Mar 7, 2:09 pm, Jerry Stuckle <jstuck...@attglobal.net> wrote:
> >>>> zackrspv wrote:
> >>>>> So, I understand how to do it, and it works for the most part; for
> >>>>> example if my code is:
> >>>>> SELECT * FROM info WHERE MATCH(term) AGAINST ('relative')
> >>>>> I get around 5 results, such as the following:
> >>>>> Relative Strength
> >>>>> Relative Return
> >>>>> Price-Earnings Relative
> >>>>> Relative Strength Index (RSI)
> >>>>> Relative Vigor Index (RVI)
> >>>>> Relative Purchase Power Parity
> >>>>> But, let us assume that we are searching for 'RSI':
> >>>>> SELECT * FROM info WHERE MATCH(term) AGAINST ('RSI')
> >>>>> When we do that, boom, no results; yet we can clearly SEE that RSI
> >>>>> does exist in the terms above.
> >>>>> How can i get it to return the result?
> >>>> *From the MySQL manual under full text searches:
> >>>> "Any word that is too short is ignored. The default minimum length of
> >>>> words that are found by full-text searches is four characters"
> >>>> RSI is, of course, shorter than that.
> >>>> --
> >>>> ==================
> >>>> Remove the "x" from my email address
> >>>> Jerry Stuckle
> >>>> JDS Computer Training Corp.
> >>>> jstuck...@attglobal.net
> >>>> ==================
> >>> now see that just sucks; how can i get it to display the results if my
> >>> clients are searching for things like RSI, RVI, ESS, etc. *Most of the
> >>> don't know what they mean, and the terms would still have to display
> >>> that. If i can't use a full text search, i'd use %like% but that'd be
> >>> pointless as it would return other things like 'RSI' for 'version' and
> >>> 'conversion'.
> >>> Any ideas on a way to get around this?
> >> Check the docs. *That's the default; you can change it. *I don't
> >> remember the exact parm offhand - never had to change it, myself.
>
> >> --
> >> ==================
> >> Remove the "x" from my email address
> >> Jerry Stuckle
> >> JDS Computer Training Corp.
> >> jstuck...@attglobal.net
> >> ==================
>
> > Well, I can't change it on my host as i'm on a shared account. *The
> > host refuses to change it as well, they consider it a security risk,
> > tho I do not see how. *There has to be an alternate way to do this,
> > such as using regular expressions, etc, but i'm not sure; how can I
> > accomplish what I need?
>
> Not easily, I'm afraid. *You might be able to do it with a regex, but
> I'm not good at regex's.
>
> And I don't really blame your hosting company - it wouldn't be a
> security risk, but searching on short words can load down the server.
>
> --
> ==================
> Remove the "x" from my email address
> Jerry Stuckle
> JDS Computer Training Corp.
> jstuck...@attglobal.net
> ==================

If the hosting is not willing to change the minimum word length, then
you can use a regular expression with a word boundary.

For instance, using your example:

SELECT * FROM info
WHERE term REGEXP '[[:<:]]RSI[[:>:]]'

Look at the following two links:

http://dev.mysql.com/doc/refman/5.0/en/regexp.html
http://dev.mysql.com/doc/refman/5.0/...-matching.html

The regular expression checking will probably be a little slower than
the fulltext search, but it might be your only choice.

I hope this helps.