How to find the records with most common tags, like the related questions in StackOverflow

algorithm, database, sql, tags

Solution

Perhaps something like:

select qt.question_id, count(*)
from   question_tags qt
where  qt.tag in
( select qt2.tag
  from   question_tags qt2
  where  qt2.question_id = 123
)
group by qt.question_id
order by 2 desc

Problem

We may tag a question with multiple tags in StackOverflow website, I'm wondering how to find out the most related questions with common tags. Assume we have 100 questions in a database, each question has several tags. Let's say user is browsing a specific question, and we want to make the system to display the related questions on the page. The criteria for related question is they have most common tags. For example: Question 1 is tagged with AAA, BBB, CCC, DDD, EEE. Question 2 is top 1 related because it also has all those 5 tags. Question 3 is top 2 related because it has only 4 or 3 tags that Questio1 has. ...... So my question is how to design the database and find out the questions that's related to Question 1 quickly. Thank you very much.

Original source