Boolean Full-Text Searches

[waqasahmed996]

I have a problem in Full Text Search. Can anyone help me please.

In Full Text Search in boolean mode if we search

'apple*'
It will find Find rows that contain words such as apple, apples, applesauce, or applet.

if we search

'apple banana'

It will find rows that contain at least one of the two words.

Is it possible that search 'apple banana' will give result same as

Select * from table where filed LIKE 'apple banana%'

means it should not get all record which has word apple OR banana whether it give only those results which has values like this

apple banana
apple banana mango
apple bananadates

one option is to use double quotes (")
but it will not give result 'apple bananadates'

please is there any solution that i find all above three record with full text search (match-against)

[Oralloy]

waqasahmed996,

Why don't you try something like this:

SELECT * FROM table
  WHERE (filed LIKE '%apple%)
        OR (filed LIKE '%banana%')

Cheers!
Oralloy

[waqasahmed996]

Thanks a lot Oralloy for your message

yes that is an option. But i want to use full text search (match-against) because i read that it is fast than LIKE. isn't it?

[Oralloy]

waqasahmed996,

I really don't know.

And, for some cases, a regular expression match may be preferable (or absolutely necessary) considering the limitations of LIKE.

Basically, the LIKE operator is a dumbed down regular expression, dating to the era when regular expressions matching was still new technology. Because of match definition, the LIKE operator is easy to code, easy to optimize, and can test in a single pass against a data value.

That said, I do not have mySQL available to test with, so I'm somewhat shooting blind.

I'd think that you can use a regular expression such as

  "\bapple.*\bbanana"

to find a word starting with "apple" followed by a word starting with "banana". But this won't find the sequence of "banana" followed by "apple".

To accomplish that, you'd need an expression similar to

  "(\bapple.*\bbanana)|(\bbanana.*\bapple)"

Unfortunately MySQL's web site is being a real pill right now, otherwise I'd research a little bit. Also, I'm not sure what degree of regular expressions MySQL supports, so I may be completely fouling you up.

Give it a whirl, and maybe we can iterate to a solution?

[waqasahmed996]

No words to say thanks. I think Regular expression have to use here. let me check all this. Thanks a lot for your guidance. you really save my a lot of time. I will be back after finding exact solution.

[Oralloy]

waqasahmed996,

Just beware that there is a trade off in complexity. If you use the long expression I wrote, it has to re-process the expression, if it doesn't find "apple" in the first place - it will look for banana and then fail, because it can't find an apple. You might want to consider using two regular expressions bound at a higher level with "and".

I really don't know how much data you have to process, but nothing beats performance testing to help tune queries. If your list is short, or the data small, it might be moot.

Good Luck!

[waqasahmed996]

Thanks again. I will test and try to write best regular expression:)