MATCH AGAINST HELP match exact word

Do you have a question? Post it now! No Registration Necessary.  Now with pictures!

Threaded View
I want to do the equivalent of

SELECT id from TABLE WHERE text='text'

only fast solution I found is:

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.


Re: MATCH AGAINST HELP match exact word

Quoted text here. Click to load it

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
   WHERE BINARY 'We demand an EXACT match!' =

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

Quoted text here. Click to load it

    "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

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:
Quoted text here. Click to load it

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

Site Timeline