MATCH AGAINST HELP match exact word

I want to do the equivalent of

SELECT id from TABLE WHERE text='text'


only fast solution I found is:


SELECT id,text from TABLE WHERE MATCH(text) AGAINST('value' IN BOOLEAN
MODE) HAVING text='value'


Is there a better way to do that? Cause using having needs me to add a
column in select wich is a problem in some cases.


It has to return the EXACT WORD MATCH. so only rows containing the
exact "value" content in the text column will be returned.


thanks

Re: MATCH AGAINST HELP match exact word

wrote in message
news:1155147292.574956.265890@b28g2000cwb.googlegroups.com.. .
> I want to do the equivalent of
>
> SELECT id from TABLE WHERE text='text'
>
>
> only fast solution I found is:
>
>
> SELECT id,text from TABLE WHERE MATCH(text) AGAINST('value' IN BOOLEAN
> MODE) HAVING text='value'
>
>
> Is there a better way to do that? Cause using having needs me to add a
> column in select wich is a problem in some cases.
>
>
> It has to return the EXACT WORD MATCH. so only rows containing the
> exact "value" content in the text column will be returned.

Normally string comparisons are not case sensitive. But you can easily
force a case sensitive comparison using the BINARY keyword:

SELECT 'abc' = 'aBc';
Returns True because it does a case insensitive compare

SELECT BINARY 'abc' = 'aBc';
Returns False because BINARY forces the compare to be case sensitive.

Your example:
SELECT id
FROM TABLE {SomeTable}
WHERE BINARY 'We demand an EXACT match!' = {SomeTextField}

If your text field is char or varchar, you can also give it the field the
BINARY attribute.
This overrides the default, case insensitive, database behaviour for string
comparisons and you can leave out the BINARY keyword from the WHERE. I
don't like this myself. It is non-standard behaviour and I would rather see
the BINARY declared explicitly in the query.

Thomas Bartkus

Re: MATCH AGAINST HELP match exact word

the problem is my field is FULLTEXT

doing just equal comparision is not fast enough.

Im dealing with big tables here...

We changed a few fields to fulltext, for searches, but that field wich
is the 'username' field needs to be exactly matched for a few things.

using MATCH AGAINST is fastest way, but I dont think this allows exact
matches for some reason. So if anyone could help I'd appreciate.

Re: MATCH AGAINST HELP match exact word

"jmichel" wrote in message
news:1155215316.915929.40370@p79g2000cwp.googlegroups.com...
> the problem is my field is FULLTEXT
>
> doing just equal comparision is not fast enough.
>
> Im dealing with big tables here...
>
> We changed a few fields to fulltext, for searches, but that field wich
> is the 'username' field needs to be exactly matched for a few things.
>
> using MATCH AGAINST is fastest way, but I dont think this allows exact
> matches for some reason. So if anyone could help I'd appreciate.
>

"FULLTEXT searches are not case sensitive."
That would disqualify FULLTEXT indexing as a means to speed up the exact,
case sensitive (BINARY!) match you demand. You requirement is for a BINARY
match.

If there is a way to have your cake and eat it too -
I'm afraid I wouldn't know it!
Thomas Bartkus

Re: MATCH AGAINST HELP match exact word

Thomas Bartkus wrote:
> "jmichel" wrote in message
> news:1155215316.915929.40370@p79g2000cwp.googlegroups.com...
> > the problem is my field is FULLTEXT
> >
> > doing just equal comparision is not fast enough.
> >
> > Im dealing with big tables here...
> >
> > We changed a few fields to fulltext, for searches, but that field wich
> > is the 'username' field needs to be exactly matched for a few things.
> >
> > using MATCH AGAINST is fastest way, but I dont think this allows exact
> > matches for some reason. So if anyone could help I'd appreciate.
> >
>
> "FULLTEXT searches are not case sensitive."
> That would disqualify FULLTEXT indexing as a means to speed up the exact,
> case sensitive (BINARY!) match you demand. You requirement is for a BINARY
> match.
>
> If there is a way to have your cake and eat it too -
> I'm afraid I wouldn't know it!
> Thomas Bartkus

forget about case sentitive thats not important, I just dont want the
exact word Im looking for:

if I search for "jessica" I dont want it to return "jessica-alba" I
just want "jessica" or "JesSiCa" or any other non case-sensitive match
of that word.

so far "jessica-alba" is returned and thats why I use the HAVING clause
too