Solved

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

Posted on 2010-11-18
7
676 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
  • 4
  • 3
7 Comments
 
LVL 51

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 51

Accepted Solution

by:
HainKurt earned 500 total points
ID: 34167302
try

match(other_information,services_information,PROFILE) against ('auti* diagnos* Auti* Diagnos*' in boolean mode)
0
3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

 
LVL 51

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 51

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Foreword In the years since this article was written, numerous hacking attacks have targeted password-protected web sites.  The storage of client passwords has become a subject of much discussion, some of it useful and some of it misguided.  Of cou…
If you find yourself in this situation “I have used SELECT DISTINCT but I’m getting duplicates” then I'm sorry to say you are using the wrong SQL technique as it only does one thing which is: produces whole rows that are unique. If the results you a…
With the power of JIRA, there's an unlimited number of ways you can customize it, use it and benefit from it. With that in mind, there's bound to be things that I wasn't able to cover in this course. With this summary we'll look at some places to go…
Learn how to create flexible layouts using relative units in CSS.  New relative units added in CSS3 include vw(viewports width), vh(viewports height), vmin(minimum of viewports height and width), and vmax (maximum of viewports height and width).

867 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

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now