Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 711
  • Last Modified:

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
0
BernardGBailey
Asked:
BernardGBailey
  • 4
  • 3
1 Solution
 
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
 
HainKurtSr. System AnalystCommented:
try

match(other_information,services_information,PROFILE) against ('auti* diagnos* Auti* Diagnos*' in boolean mode)
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
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

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now