Mysql match against - making indexes


I have 2 tables:

  • products
  • sites

I want to join tables by products.site_id = and preform MATCH AGAINST search on joined table on 2 fields:

I’m not sure how this should be done with mysql.

Both title fields are varchar types, which means I should create indexes for them.

I tried doing index for each:
CREATE INDEX title_index ON products(title)
CREATE INDEX title_index ON sites(title)

But now even this won’t work:

SELECT * FROM products WHERE MATCH (title) AGAINST ('search string')

It will give me:

#1191 - Can’t find FULLTEXT index matching the column list

Any help how to solve this issue is highly appreciated.


to answer your main question first, you got the message about the missing fulltext index because you don’t have a fulltext index

fulltext indexes are different from the indexes you created on the title columns

the bad news is, fulltext indexes cannot span tables, they are restricted to a single table

in any case, a column like “title” is probably only a varchar, likely no greater than a couple hundred bytes

fulltext indexes are designed for searching within large blocks of text, such as book chapters

you should be fine using LIKE instead of MATCH AGAINST