I have these two tables (products and brands table) and I need to create a fulltext index to support the search function for my website. I want an index that contains the brand name and product name of a product. And for this case, the columns come from different tables.

What I know for now is creating fulltext indexes from a single or multiple columns coming from just one table.

This is the preview of the database i am currently working on: Database Preview Here

2 Answers

Bill Karwin On

MySQL doesn't support any type of index covering multiple tables. All columns of an index must come from a single table.

If you need to create a search engine over a multi-table dataset, you might consider exporting the result of a JOIN query to a fulltext search engine like Sphinx Search or Solr (which is the software inside ElasticSearch).

You might like my presentation Full Text Search Throwdown.

Rick James On

Perhaps you need to do the following...

Gather all the columns you are searching on into a single table. (Either move them to this new table or make copies.) This table would also have an id to link back to the main table.

Then focus on optimizing the search activity using that table.

Note that in copying columns into the table, you could do some cleansing (removal of kruft that interferes with searching, such as html tags, alternate spellings, etc).

Looking at your schema, I almost think this is a case where normalization should not have been done.