Query for getting related entries ?

Warlock

I want to when opening video to display related videos which contain similar title as opened video. I checked one site which have related videos and what i noticed how every related video which is showing having a minimum 2 same words, while most of those related videos had atleast 3 same words or even 5, but none less then 2.

Here is example of how it should look:

So let say a video title would be title like this: Funny yellow cat is jumping on long roof

And related videos would be something like this:

Funny cat compilation
Yellow cat jumping on floor
Cat on long roof
Cat jumping on ice
Cat and dog chasing on yellow roof

sneakyimp

I asked a similar question about nine years ago:
http://board.phpbuilder.com/showthread.php?10340672-MySQL-query-to-find-similar-results

Things have changed since then. I'm not sure, but I think you might benefit from using MySQL's full-text search functions. Take a look at this:
http://dev.mysql.com/doc/refman/5.7/en/fulltext-natural-language.html

Warlock

sneakyimp;11054883 wrote:
I asked a similar question about nine years ago:
http://board.phpbuilder.com/showthread.php?10340672-MySQL-query-to-find-similar-results

Things have changed since then. I'm not sure, but I think you might benefit from using MySQL's full-text search functions. Take a look at this:
http://dev.mysql.com/doc/refman/5.7/en/fulltext-natural-language.html

Hmm, your last post says " i might make query like this", so did that query worked that way 🙂

And yes i am aware of FT feature, but i read an article which made benchmark of all known search engines and apparently spinx is fastest when it comes to reading performance, and that is what i aim. Tho, it will take some time to figure out to implement spinx, so i want to have working query as
temporal solution 🙂

sneakyimp

It's been 9 years (!) so I don't remember what I ended up doing. I do remember that it takes a bit of PHP juggling to construct a query if you rely on LIKE or REGEXP to construct your own query. User tomhath had a good suggestion here:
http://board.phpbuilder.com/showthread.php?10340672-MySQL-query-to-find-similar-results&p=10804077&viewfull=1#post10804077

dalecosp

Warlock;11054929 wrote:
Hmm, your last post says " i might make query like this", so did that query worked that way 🙂

And yes i am aware of FT feature, but i read an article which made benchmark of all known search engines and apparently spinx is fastest when it comes to reading performance, and that is what i aim. Tho, it will take some time to figure out to implement spinx, so i want to have working query as
temporal solution 🙂

I don't recall Sphinx as being terrible to learn or install. It did take some time, though, and some thought, and sudo privileges 😉 😉

Without it, you're probably stuck with LIKE searching, or REGEXP searching, and you'll have to spend brain power and time figuring out how to pick the correct words from your title in order to create a search string that actually returns the sort of results you want to see.

I might go ahead and go for the silver bullet and implement Sphinx; you'll spend at least part of the time you could be spending implementing Sphinx to implement your temporary solution.

lilysmith22

Yeah this is happening by keeping in mind the user mindset. What fits best, the relevancy between the title and video.