Mysql match against - making indexes

Databases
1

Hello,

I have 2 tables:

  • products
  • sites

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

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.

Thanks

2

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