Re: Full-Text Search Across Multiple Fields
Posted by:
Rick James
Date: August 22, 2015 01:15PM
+James
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.