I'm using MySQL's boolean full-text search for my search engine: https://dev.mysql.com/doc/refman/5.6/en/fulltext-boolean.html.
Assume I have a table with the following columns:
PRODUCTS -------- id (PK) color_id (FK to COLORS table) name description
When given a search term, I want to search the product's table's
description columns, as well as the product's color name. The color's name is in a separate table.
Should I create a new column in the product's table and put all the text I want indexed in there? For example, I add a
full_text_index column, and dump a concatenation of
description, and the color's name in that column? Then do full-text search against that column?
UPDATE: Or, instead of a column in an existing table, maybe create a new table to contain all the text that should be used for the full-text search. That table can just have a FK to the