CODEWITHSUNDEEP
mysqlpartialfull-text-search
user1094786
user1094786

Reputation: 6820

Is MySQL FULLTEXT best solution for partial words?

I have a MySQL MyISAM table containing entries that describe airports. This table contains 3 varchar columns - code, name and tags.

code refers to the airport's code (like JFK and ORD), the name refers to the airport's name (John F Kennedy and O'Hare) and tags specify a semicolon separated list of tags that are associated with the airport (like N.Y.C;New York; and Chicago;).

I need to be able to lookup an airport (for an autocomplete) by either the code, name or tags, therefore I set a FULLTEXT index on (code, name, tags).

I have encountered two problems with FULLTEXT so far that prevent me from working with it:
1. There is no way to do partial matching - only postfix matching (is this true?)
2. When a period ('.') is specified in the term to match against, the matching works differently. I am assuming that the period is being parsed in a special way. For example, doing a FULLTEXT search on N.Y.C will not return JFK, although doing the same search on New York will

Is there anyway to overcome these barriers? Otherwise, should I be looking at like matching instead, or an entirely different storage engine? Thanks!

Upvotes: 1

Views: 651

Answers (1)

user1094786
user1094786

Reputation: 6820

Best solution I came up with is using both FULLTEXT and like matching, and using UNION for the results.

Upvotes: 1

Related Questions