改善MySQL相关文章查询 [英] improving MySQL related articles query

问题描述

对于相关主题列表，我使用带有标签的查询.它显示5篇文章的列表，这些文章具有1个或多个共同的标签，并且早于所查看的标签.

For a related topic list I use a query using tags. It displays a list of 5 articles that have 1 or more tags in common and that are older than the viewed one.

是否可以编写一个查询，通过赋予具有共同的2,3,4 ...标签的文章更多的权重来产生更相关的结果?

Is it possible to write a query that produce more relevant results by giving more weight to articles that have 2,3,4... tags in common?

我在差不多相同的主题上看到了这个主题: MySQL查找相关文章

I saw this topic on more or less the same subject: MySQL Find Related Articles

，但是如果公共标签少于3个，则会产生0个结果.

but it produces 0 results in the case there are less than 3 tags in common.

我现在使用的查询:

SELECT DISTINCT
AAmessage.message_id, AAmessage.title, AAmessage.date 
FROM
AAmessage
LEFT JOIN
AAmessagetagtable 
AS child ON child.message_id = AAmessage.message_id 
JOIN AAmessagetagtagtable 
AS parent ON parent.tag_id = child.tag_id 
AND
parent.message_id = '$message_id' 
AND AAmessage.date < '$row[date]' 
ORDER BY 
AAmessage.date DESC LIMIT 0,5

使用表格:

AAmessage (message_id, title, date...)

AAmessagetable (key, message_id, tag_id)

AAtag (tag_id, tag.... not used in this query but needed to store names of tags)

推荐答案

首先，请原谅我将表名更改为message和message_tag以提高可读性.

First of all, please excuse that I changed the table names a bit to message and message_tag for readability.

第二，我没有对此进行测试.使用它而不是确定的答案作为指针.

Second, I didn't test this. Use it rather as a pointer than a definite answer.

该查询使用两个子查询，这些子查询可能效率不高，可能还有改进的余地.首先，最里面的查询查找当前消息的标签.然后，中间查询查找标记有至少一个公共标签的消息.分组用于获取唯一的message_id并按通用标签数对其进行排序.最后，JOIN用于加载其他详细信息并过滤掉旧消息.

The query uses two subqueries, which might not be so efficient, there is probably a room for improvement. First, the innermost query looks for the tags of the current message. Then, the middle query looks for messages which are marked with at least one common tag. The grouping is used to get unique message_id and order them by number of common tags. Last, the JOIN is used to load additional details and to filter out the old messages.

您可能会注意到我使用问号而不是'$xyz'.这是为了避免转义变量内容的麻烦.

You may notice I used question marks instead of '$xyz'. This is to avoid the care about escaping the variable contents.

SELECT message_id, title, date
FROM message
RIGHT JOIN (SELECT message_id, COUNT(*)
            FROM message_tag
            WHERE tag_id IN 
                (SELECT MT.tag_id FROM message_tag MT WHERE MT.message_id = ?)
            GROUP BY message_id
            ORDER BY COUNT(*) DESC) RELATED_MESSAGES
            ON message.message_id = RELATED_MESSAGES.message_id
WHERE date < ?

这篇关于改善MySQL相关文章查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！