Fulltext index with ids from another table

Question

I have a product table where the rows are connected to one or multiple rows in a category table by category_id. If I would like to make those category names searchable, is there any solution to specify the fulltext index to be created of the names in category table that corresponds to the ids in the category columns in the rows of product table?

Answer 1

Yes, if you add a full text index on category_name, you can get a list of products with a certain category name (search) as so:

SELECT p.name FROM products p INNER JOIN categories c
ON p.category_id = c.category_id
WHERE MATCH (c.category_name) AGAINST ('Some Category');