?
Solved

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

Posted on 2010-11-18
7
Medium Priority
?
695 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 57

Expert Comment

by:HainKurt
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 57

Accepted Solution

by:
HainKurt earned 2000 total points
ID: 34167302
try

match(other_information,services_information,PROFILE) against ('auti* diagnos* Auti* Diagnos*' in boolean mode)
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 57

Expert Comment

by:HainKurt
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 57

Expert Comment

by:HainKurt
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

New benefit for Premium Members - Upgrade now!

Ready to get started with anonymous questions today? It's easy! Learn more.

Question has a verified solution.

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

This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
Suggested Courses

770 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