mysql - 从多个表mysql中查找最近的日期

Question

我有很多表记录用户在某些论坛上的操作，每个日志事件都有它的日期。我需要一个查询，为我提供去年不活跃的所有用户。我有以下查询(工作查询):

SELECT *
FROM (questions AS q
    INNER JOIN Answers AS a
    INNER JOIN bestAnswerByPoll AS p
    INNER JOIN answerThumbRank AS t
    INNER JOIN notes AS n
    INNER JOIN interestingQuestion AS i ON q.user_id = a.user_id
    AND a.user_id = p.user_id
    AND p.user_id = t.user_id
    AND t.user_id = n.user_id
    AND n.user_id = i.user_id)
WHERE DATEDIFF(CURDATE(),q.date)>365
    AND DATEDIFF(CURDATE(),a.date)>365
    AND DATEDIFF(CURDATE(),p.date)>365
    AND DATEDIFF(CURDATE(),t.date)>365
    AND DATEDIFF(CURDATE(),n.date)>365
    AND DATEDIFF(CURDATE(),i.date)>365

我在该查询中正在做什么 - 根据 userId 连接所有表，然后检查每个表分别查看日期列以查看是否超过一年

我想知道是否有一种方法可以使它更简单，比如找到所有日期(最新日期)之间的最大值，然后将这个日期与当前日期进行比较

Answer 1

如果你想获得最好的性能，你不能使用greatest()。而是做这样的事情:

SELECT *
FROM questions q
JOIN Answers             a  ON q.user_id = a.user_id
JOIN bestAnswerByPoll    p  ON a.user_id = p.user_id
JOIN answerThumbRank     t  ON p.user_id = t.user_id
JOIN notes               n  ON t.user_id = n.user_id
JOIN interestingQuestion i  ON n.user_id = i.user_id
WHERE q.date > curdate() - interval 1 year
  AND a.date > curdate() - interval 1 year
  AND p.date > curdate() - interval 1 year
  AND t.date > curdate() - interval 1 year
  AND n.date > curdate() - interval 1 year
  AND i.date > curdate() - interval 1 year

您希望避免使用 datediff()，这样 MySQL 就可以在日期列比较时进行索引查找。现在，为确保索引查找有效，您应该在 (user_id, date) 上为每个表创建复合(多列)索引。

在这个复合索引中，第一部分 (user_id) 将用于更快的连接，第二部分 (date) 将用于更快的日期比较。如果您将 SELECT * 中的 * 替换为仅上面提到的列(如仅 user_id)，您可能能够获得仅索引扫描，这将是超快的。

UPDATE 不幸的是，MySQL 不支持像 PostgreSQL 和其他一些数据库这样的公用表表达式的 WITH 子句。但是，您仍然可以按如下方式分解出常用表达式:

SELECT *
FROM questions q
JOIN Answers             a  ON q.user_id = a.user_id
JOIN bestAnswerByPoll    p  ON a.user_id = p.user_id
JOIN answerThumbRank     t  ON p.user_id = t.user_id
JOIN notes               n  ON t.user_id = n.user_id
JOIN interestingQuestion i  ON n.user_id = i.user_id,
(SELECT curdate() - interval 1 year AS year_ago) x
WHERE q.date > x.year_ago
  AND a.date > x.year_ago
  AND p.date > x.year_ago
  AND t.date > x.year_ago
  AND n.date > x.year_ago
  AND i.date > x.year_ago