MYSQL MATCH(other_information) AGAINST('Auti*' IN BOOLEAN MODE) for multiple word roots.

Hi team,

I am trying to find all the records which include the word roots for 'autism' or 'diagnosis' or any root extension thereon in the fields 'profile', 'services_information', 'other_information'.

SELECT
provider1_providers.provider_name,
provider1_providers.`profile`,
provider1_providers.services_information,
provider1_providers.other_information
FROM
provider1_providers
where
MATCH(other_information) AGAINST('Auti*' IN BOOLEAN MODE)
or MATCH(other_information) AGAINST('Diagnos*' IN BOOLEAN MODE)
or MATCH(services_information) AGAINST('Auti*' IN BOOLEAN MODE)
or MATCH(services_information) AGAINST('Diagnos*' IN BOOLEAN MODE)
or MATCH(PROFILE) AGAINST('Autis*' IN BOOLEAN MODE)
or MATCH(PROFILE) AGAINST('Diagnos*' IN BOOLEAN MODE)
order by provider_name


I have a Fulltext index set on the three fields 'profile', 'services_information', 'other_information' and 104 records are returned.

Is there a better way to do this than to use separate 'OR' condition statements ?

All help is appreciated
Cheers Bernard
BernardGBaileyAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
HainKurtConnect With a Mentor Sr. System AnalystCommented:
try

match(other_information,services_information,PROFILE) against ('auti* diagnos* Auti* Diagnos*' in boolean mode)
0
 
HainKurtSr. System AnalystCommented:
add all columns together

match(col1,col2,col3) against ('auti diagnos' in boolean mode)
0
 
BernardGBaileyAuthor Commented:
Sorry

match(other_information,services_information,PROFILE) against  ('auti diagnos' in boolean mode)

returns 0 records whereas

match(other_information,services_information,PROFILE) against ('Auti*' in boolean mode)
or match(other_information,services_information,PROFILE) against ('Diagnos*' in boolean mode)

returns 33 records

and
 
match(other_information,services_information,PROFILE) against ('auti*' in boolean mode)
or match(other_information,services_information,PROFILE) against ('diagnos*' in boolean mode)

returns 79 records

and

MATCH(other_information) AGAINST('Auti*' IN BOOLEAN MODE)
or MATCH(other_information) AGAINST('Diagnos*' IN BOOLEAN MODE)
or MATCH(services_information) AGAINST('Auti*' IN BOOLEAN MODE)
or MATCH(services_information) AGAINST('Diagnos*' IN BOOLEAN MODE)
or MATCH(PROFILE) AGAINST('Autis*' IN BOOLEAN MODE)
or MATCH(PROFILE) AGAINST('Diagnos*' IN BOOLEAN MODE)

returns 104 records

So is there a better way?
0
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
HainKurtSr. System AnalystCommented:
how many records you should get? and how many you get from 34167302?
0
 
BernardGBaileyAuthor Commented:
Hi HainKurt,

match(other_information,services_information,PROFILE) against ('auti* diagnos* Auti* Diagnos*' in boolean mode)  returned 104 records.

Looks like the solution

Just checking through NOT() to find exactly how many I should get.

Thanks
Bernard

PS was 34167302 a misskey of pi  ie 3.4167302?
0
 
HainKurtSr. System AnalystCommented:
34167302 is the post number ;) posted by me @ 18/11/10 02:38
0
 
BernardGBaileyAuthor Commented:
Thanks for this valuable insight
Cheers
Bernad
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.