MySQL Forums
Forum List  »  Full-Text Search

Re: Full-Text Search Across Multiple Fields
Posted by: Rick James
Date: August 22, 2015 01:15PM


correctly matched "456 James Ct", which is one of the 3 fields you are searching against.

If you need something more complex than "find any of these words in any of these columns", then I suggest this technique:

MATCH(name, address, city) AGAINST ('QUERY' IN BOOLEAN MODE)
AND somethingelse

The MATCH needs FULLTEXT(name, address, city). It will probably be done first and should be designed to get potentially more rows than desired.
Then the "sometingelse" is another FT query, a LIKE, a REGEXP, or anything else, to further filter.

For example, if I wanted specifically 456 James Ct, not 456 James St, I might do
MATCH ... AGAINST ('+James')
AND address LIKE '%James Ct%'.

The MATCH would be fast; the LIKE, although slow, would be applied to only a few rows, hence not too much a burden.

Options: ReplyQuote

Written By
Re: Full-Text Search Across Multiple Fields
August 22, 2015 01:15PM

Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.