mysql - 我怎样才能告诉 MySQL 在查询期间只检查最新的 x 个条目？

Question

我有一个页面可以选择我数据库中的所有用户。只有一千两个。没什么大不了的。

但是，在选择它的同时，它还会使用该查询中的 uid 来检查另一个包含大约 25,000 个条目的表。

SELECT COUNT(id)
FROM logs
WHERE time+date > {$timeNow} AND uid={$row['id']}

它针对每个用户条目执行此操作。可以想象，这会占用大量资源。

上面的 WHERE 子句可能只适用于最后一天的条目，最多可能是 500-1000。然而，它的影响远不止于此。

我在想我可以设置一个 cronjob，每天一次或两次将所有不匹配 WHERE 子句的条目导出到另一个表。我知道这将极大地帮助甚至以某种有效的方式解决问题。但是，我真的不喜欢为了相同(相对)的目的而使用两个表。

我有更好的方法吗？我已经四处搜索了很长时间，但我找不到任何东西，但我想我会问问你们，以防你们遇到同样的问题并找到解决它的独特方法。

编辑 对于 Brendan Long:我的新查询:

$SQL = "SELECT u.id, COUNT(l.id) " .
       "FROM users u " .
       "INNER JOIN logs l " .
       "ON l.uid = u.id " .
       "WHERE l.time+l.date > {$timeNow} " .
       "GROUP BY u.id";

此外，请不要因为缺少 PDO 而抨击我。我还没有时间把它转换过来。我知道我是一个糟糕的人。

Answer 1

使用 JOIN这样数据库就可以为您优化它作为一个查询:

SELECT u.uid, COUNT(l.id)
FROM Users u -- or whatever your users table is named
LEFT JOIN logs l
ON l.uid = u.uid AND l.time + l.date > $timeNow
GROUP BY u.uid

用英语来说，这会告诉数据库，“给我一个用户 ID 列表和与之关联的日志数量，其中 time + date 在 $timeNow 之后”。这样效率要高得多，因为您一次性将所有工作交给数据库，因此它可以找出获取所有信息的最佳方式，而不是一次只抓取一条信息.

加入

LEFT JOIN 告诉数据库通过查找用户表和日志表具有相同 uid 的记录来匹配用户和日志。 LEFT JOIN 中的 LEFT 告诉数据库为用户(连接的左侧 侧)返回结果，即使他们不这样做没有任何与之关联的日志(连接的右侧)。如果您不想看到没有用户日志的结果，您可以执行 INNER JOIN，这将只显示在连接两边都匹配的结果(两个用户和至少一条日志消息)。

分组依据

GROUP BY 是按用户 ID 对结果进行分组所必需的——否则您只会得到与任何 用户关联的日志消息总数，即大概没有帮助，因为您可以SELECT COUNT(*) FROM logs。

我正在使用表别名来缩短查询，因为这是我一直使用的样式，但您可以轻松地只输入表的全名(logs.uid 等)。 ).您甚至可以不包含表名而逃脱，但是当您在查询中引用存在于多个表中的列时，您的数据库会感到困惑，所以我发现最简单的方法是始终明确说明您的哪一列正在谈论。

索引

这个新查询应该立即完成，除非你有一个非常大的数据库。如果没有，请采纳@charly 的建议并尝试一些索引。不幸的是，您在使用该值之前添加了 l.time + l.date，我认为 MySQL 不会让您在 l.time + l.date 上创建索引>，但您可以通过首先过滤 l.date(可索引)来获得不错的结果:

ON l.uid = u.uid AND l.date > $timeNow AND l.time + l.date > $timeNow

这看起来是重复的，但它给了数据库更多的工作空间，因为它可以:

1. 获取 l.date 在 $timeNow 之后的结果使用索引。
2. 使用 l.time + l.date > $timeNow 过滤那组(希望是小的)结果。

代替:

1. 对于表中的每条记录，添加l.time + l.date。
2. 检查该结果是否在 $timeNow 之后

PHP

要在 PHP 中执行此操作，您需要执行如下操作:

$sql = // that query above
$result = mysql_query($sql);
while($row = mysql_fetch_array($result)) {
    echo "User " . $row[0] . " posted " . $row[1] . " times.";
}

或者，如果您需要以更复杂的方式使用它，请预先获取所有内容:

$counts = array();
$sql = // that query above
$result = mysql_query($sql);
while($row = mysql_fetch_array($result)) {
    $counts[$row[0]] = $row[1];
}

// later
$user = 5; // some user we care about
echo "User " . $user . " posted " . $counts[$user] . " times.";

如果您以“预先全部获取”的方式执行此操作，您还可以通过使用查询的 INNER JOIN 版本进行一些优化，因为任何用户都不在 $ counts 的计数为 0。

抱歉，如果我的语法有误，但我认为这说明了这个想法。

安全说明

在次要切线上:看起来您正在将变量直接放入查询中，即 generally a bad idea .有许多 incredibly complicated solutions , 但最简单的就是 use parametrized queries永远不要将变量直接放入您的 SQL 中。