Solved

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

Posted on 2010-11-18
7
691 Views
Last Modified: 2012-05-10
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
Comment
Question by:BernardGBailey
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
7 Comments
 
LVL 55

Expert Comment

by:Huseyin KAHRAMAN
ID: 34165963
add all columns together

match(col1,col2,col3) against ('auti diagnos' in boolean mode)
0
 

Author Comment

by:BernardGBailey
ID: 34167056
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
 
LVL 55

Accepted Solution

by:
Huseyin KAHRAMAN earned 500 total points
ID: 34167302
try

match(other_information,services_information,PROFILE) against ('auti* diagnos* Auti* Diagnos*' in boolean mode)
0
Business Impact of IT Communications

What are the business impacts of how well businesses communicate during an IT incident? Targeting, speed, and transparency all matter. Find out more in this infographic.

 
LVL 55

Expert Comment

by:Huseyin KAHRAMAN
ID: 34167309
how many records you should get? and how many you get from 34167302?
0
 

Author Comment

by:BernardGBailey
ID: 34167433
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
 
LVL 55

Expert Comment

by:Huseyin KAHRAMAN
ID: 34168250
34167302 is the post number ;) posted by me @ 18/11/10 02:38
0
 

Author Closing Comment

by:BernardGBailey
ID: 34170321
Thanks for this valuable insight
Cheers
Bernad
0

Featured Post

Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this series, we will discuss common questions received as a database Solutions Engineer at Percona. In this role, we speak with a wide array of MySQL and MongoDB users responsible for both extremely large and complex environments to smaller singl…
In this blog post, we’ll look at how ClickHouse performs in a general analytical workload using the star schema benchmark test.
There's a multitude of different network monitoring solutions out there, and you're probably wondering what makes NetCrunch so special. It's completely agentless, but does let you create an agent, if you desire. It offers powerful scalability …
Monitoring a network: why having a policy is the best policy? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the enormous benefits of having a policy-based approach when monitoring medium and large networks. Software utilized in this v…

726 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question