7
SELECT 
* 
FROM 
company c
INNER JOIN 
city ci ON ci.city_id = c.city_id
INNER JOIN 
state s ON s.state_id = c.state_id
WHERE 
MATCH (
c.name, ci.name, c.zipcode, s.name
)
AGAINST (
'los angeles'
)
marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
vick
  • 939
  • 1
  • 8
  • 14
  • 1
    This question is very related: http://stackoverflow.com/questions/2378366/mysql-how-to-make-multiple-table-fulltext-search Although I don't think they are dupes, since this question doesn't "pre-know" about a possible problem due to `JOIN`. – ebyrob Mar 17 '17 at 17:20

2 Answers2

18

All columns in MATCH (...) must be from the same table, because they must be fulltext indexed, and MySQL cannot create one index for data in multiple tables.

Messa
  • 24,321
  • 6
  • 68
  • 92
12

I had the same problem and solved it like this:

SELECT 
* 
FROM 
company c
INNER JOIN 
city ci ON ci.city_id = c.city_id
INNER JOIN 
state s ON s.state_id = c.state_id
WHERE 
   MATCH (c.name) AGAINST ('los angeles')
OR MATCH (ci.name) AGAINST ('los angeles')
OR MATCH (c.zipcode) AGAINST ('los angeles')
OR MATCH (s.name) AGAINST ('los angeles')

But as I see, You are searching in simple fields like 'name' and 'zipcode'. As for me, it would be better to use LIKE and just concatenate them

SELECT 
* 
FROM 
company c
INNER JOIN 
city ci ON ci.city_id = c.city_id
INNER JOIN 
state s ON s.state_id = c.state_id
WHERE 
CONCAT_WS (' ', c.name, ci.name, c.zipcode, s.name)
LIKE ('%los angeles%')
Karfax
  • 233
  • 1
  • 3
  • 6
  • 1
    Be careful using CONCAT_WS like that will match results like a company named "Hellos" in a city named "Angeles". An unlikely bug to encounter, but still a potential issue. – Code Commander Feb 15 '17 at 00:20
  • This should really be marked as the answer. This is a very clever way around the single table limitation. Moving from MySQL MyISAM to MariaDB InnoDB with Full Text Search this is a quick way to "port" your original queries. – Yves Dorfsman Jan 05 '18 at 22:09