Matching text on full word or start of word with MySQL

Hi,

I am trying to build a query that matches on  the start of a word or the full word for a keyword search. So a typical SQL query using LIKE would be WHERE `field` LIKE "ENT%" OR `field` LIKE "ENT". The issue I have is that the query result I get is matching on partial words. So I have a number of customers that have a high result of partial words and hence get ranked highly in the search but are in no way actually related to ENT (Ear nose & throat)

Any ideas as to how I could make the search results more relevant using MySQL fulltext and LIKE statements? My understanding of MySQL fulltext is "+ENT" means full word ENT and "+ENT*" means the letters ENT followed by wildcard e.g. LIKE "ENT%"

e.g.
1) Accident
2) Centre
3) patients
4) Entrapment

Here is my example query:

SELECT l.link_id, l . * , c . * , ( 1.4 * (
MATCH (
link_name
)
AGAINST (
'+ENT*'
IN BOOLEAN
MODE
) ) ) + ( .66 * (
MATCH (
link_desc
)
AGAINST (
'+ENT'
IN BOOLEAN
MODE
) ) ) + ( .44 * (
MATCH (
link_desc
)
AGAINST (
'+ENT*'
IN BOOLEAN
MODE
) ) ) AS score
FROM (
health4_mt_links AS l, health4_mt_cl AS cl
)
LEFT JOIN health4_mt_cats AS c ON c.cat_id = cl.cat_id
WHERE link_published = '1'
AND link_approved = '1'
AND (
publish_up = '0000-00-00 00:00:00'
OR publish_up <= '2015-04-01 00:12:34'
)
AND (
publish_down = '0000-00-00 00:00:00'
OR publish_down >= '2015-04-01 00:12:34'
)
AND cl.link_id = l.link_id
AND (
(

MATCH (
link_desc, link_name
)
AGAINST (
'+ENT*'
IN BOOLEAN
MODE
)
)
)
AND main =1
GROUP BY l.link_id
ORDER BY link_featured DESC , l.`value` DESC , score DESC , l.sort_field, link_name

Open in new window

Screen-Shot-2015-04-01-at-1.31.19-pm.png
Jeremy LeysTechnical LeadAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

chaauCommented:
You do not need any modifiers, just use 'ENT'

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Jeremy LeysTechnical LeadAuthor Commented:
You are right, however "+" e.g. "+ENT specialist" is needed when adding phrase matching so I have made this conditional and this is working nicely. The simplest solutions are the best ones.

Thank you :)
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
MySQL Server

From novice to tech pro — start learning today.