How to find 'similar' records in a MySQL table based on 'title' and 'description' columns?

Question

I have a MySQL table storing some user-generated content. For each piece of content, I have a title (VARCHAR 255) and a description (TEXT) column.

When a user is viewing a record, I want to find other records that are 'similar' to it, based on the title/description being similar.

What's the best way to go about doing this? I'm using PHP and MySQL.

My initial ideas are:

1) Either to strip out common words from the title and description to be left with 'unique' keywords and then find other records that share those keywords.

E.g in the sentence: "Bob woke up at 5 am and went to school", the keywords would be: "Bob, woke, 5, went, school". Then if there's another record whose title talks about 'bob' and 'school', they would be considered 'similar'.

2) Or to use MySQL's full-text search, though I don't know if this would be any good for something like this?

Which method would be better out of the two, or is there another even better method?

Answer 1

MySQL supports full-text search with MyISAM (not InnoDB) engine. A full description of the options available when querying the DB is available in the following link: https://dev.mysql.com/doc/refman/8.0/en/fulltext-search.html  

The query can automatically get rid of common stop-words and words too common in the data set (more than 50% of the rows contain them) depending on the querying method. Query expansion is also accessible and the query type should be decided depending on your needs.

If you wish to Learn about MySQL visit this My SQL Training.