在连接查询中按日期排序和计算

Question

我正在做一个项目，我在网站上跟踪用户，我记录他们在网站上的每次点击。每当他们点击 URL 时，我都会在数据库中创建它，并用一些标签对其进行标记。

在我的数据库中，每个 URL 都被命名为“资源”，并且一个资源可以使用多个标签进行标记。访问者在访问 URL 时会连接到资源，当用户点击资源时，我也会将日期连接到该资源。

我想做的事情是找到带有正确标签的资源，例如本月或今天观看过的资源。

我当前正在构建的查询位于此处：

SELECT r.resource_id, r.resource_url
FROM resource r
JOIN visitor_resource vt ON vt.resource_id = r.resource_id
JOIN resource_tags rt ON rt.resource_id = vt.resource_id
JOIN tags t ON t.tag_id = rt.tag_id AND t.tag_name =  '42'
GROUP BY r.resource_id

为了让您了解结构，您可以在此处看到： 跟踪数据库结构 http://kaspergrubbe.dk/db-overview.png

所以基本上我会必须通过查看上个月的visitor_resources.last_visited 来计算给定月份内有多少visitor_resources，并获取访问量最大的5 个资源。

如何处理这个问题？

如果没有查询缓存，上面的查询看起来也很慢，我怀疑这是因为 t.tag_name 不是索引，而是一个 varchar，但是除了添加这个索引之外，还有什么办法可以加快进程吗？

谢谢。

Answer 1

您遗漏了任何基于日期的条件，因此您应该添加该条件并查看性能如何变化。另外，如果您正在寻找计数，那么您也应该添加该计数。我认为 mySQL 支持 LIMIT 子句（而不是 TOP），因此添加该子句以将其限制为 5 个最常访问的资源。将所有内容放在一起，它可能看起来像这样：

SELECT
    r.resource_id,
    r.resource_url,
    COUNT(*)
FROM
    Visitor_Resources VR
INNER JOIN Resources R ON R.resource_id = VR.resource_id
INNER JOIN Resource_Tags RT ON RT.resource_id = R.resource_id
INNER JOIN Tags T ON
    T.tag_id = RT.tag_id AND
    T.tag_name = '42'
WHERE
    VR.last_visited BETWEEN <start of month> AND <end of month>
GROUP BY
    r.resource_id,
    r.resource_url
ORDER BY
    COUNT(*) DESC
LIMIT 5

抱歉，这些天我没有做很多 mySQL，所以我不知道最后一行中的开始和结束日期参数会是什么样子。

除非您的标签表非常大，否则索引可能并不重要。不过，Visitor_Resources.last_visited 上的索引可能是个好主意。

另外，我更改了查询中的表名称以使其更加一致。我个人喜欢复数名称，但单数也可以。不管你选择哪一个，选择一个并坚持下去。

Answer 2

Kasper，

首先您需要调整您的 JOIN。没有必要在每一行都说 JOIN ON：

ie.
        LEFT JOIN(
        groups, sign
        )ON(
        user.user_id = groups.userID AND
        groups.group_id = sign.groupID

尝试类似的连接。

然后向子句添加 ORDER BY。

ORDER BY last_visited DESC LIMIT 5;

这将对您的日期表进行排序，并从您指定的列中获取最近的 5 个条目。

希望这有帮助。