word likeness/similarity in mysql

I have a column that holds the genre of a movie (up to three specifiers separated by slashes) such as "comedy/romance/adventure." I开发者_JAVA百科s there a function or something similar in mysql or php that would allow me to take the genre of a movie and compare it with other genres of other rows and arrange them by likeness? For example, having a movie with "comedy/romance/adventure" would return movies with all three first, and then movies with 2 of those genres, and finally movies with maybe 1 of those genres.

If you enable full-text indexing on the genre column, you can do it. I would recommend using an external full-text search engine such as sphinx to handle this, though, as MySQL's built-in full-text indexing really ain't that great.

You'd start by setting a full-text index on the genre field

ALTER TABLE movies ADD FULLTEXT INDEX (genre);

Then you'd be able to select from this like so:

SELECT *, MATCH(genre) AGAINST ('comedy romance adventure') AS relevancy FROM movies ORDER BY relevancy DESC;

I'd also suggest a full-text index like Sphinx or Apache Solr for relevance search.

There are a number of related posts on the subject.