I've faced the following "problem"...
Let's imagine that we have to develop some kind of blogging software.
Tags are associated with posts in following DB structure:
table: posts
post_id
post_body
table: tags
tag_id
tag_title
table: tags2posts
post_id
tag_id
Let's imagine that I have 4 posts in my DB.
post1 is having tags: tag1, tag2, tag3
post2 is having tags: tag1, tag2, tag3, tag4
post3 is having tags: tag1, tag2
post4 is having tags: tag4
Let's imaging that we are currently viewing post1 and we want to be shown related by tag posts.
So, we have to select from DB posts having maximum number of the same tags as post1 have.
1. Try to select all posts which are having all 3 tags
2. Try to select all posts which are having 2 tags (tag1, tag2 | tag2, tag3 | tag3, tag1)
3. Try to select all posts which are having only one tag
As you can see - it is really difficult to create worth viewing related posts list. It takes too much SQL queries.
Is there any way to decrease number of SQL queries and increase query time?
Thank you.
P.S.: I'm really sorry for my English, but I hope my text is clear for everybody.