mysql - 通过 MySQL 使用 WHERE 跨两个表进行大表查询

Question

我有两个大 table ，两个 table 上都有一个 venue_id 。它不是外键，因为它们是两个独立的值。它们如下:

table: activity_retail
columns: activity_id, venue_id, created_at
indexes: activity_id (PRIMARY), (activity_id, venue_id) INDEX


table: activity
columns: activity_id, item_id, venue_id, created_at
indexes: activity_id (PRIMARY), (created_at) INDEX

我想对它们进行查询，查询每个表中 venue_id 等于 X 的所有事件。所以我会这样做:

    SELECT * from activity
    LEFT JOIN activity_retail on activity_retail.activity_id = activity.activity_id
    WHERE activity_retail.venue_id = X or activity.venue_id = X 
ORDER BY activity_retail.created_at desc LIMIT 0, 25

我可能还需要执行此分页查询:

    SELECT * from activity
    LEFT JOIN activity_retail on activity_retail.activity_id = activity.activity_id
    WHERE activity_retail.venue_id = X or activity.venue_id = X and activity.activity_id > Y
ORDER BY activity_retail.created_at desc LIMIT 0, 25

情况是activity_retail中的activity_id可能不存在，它是一个可选字段，所以我不能做内连接，必须是左连接。

这是解释:

1   PRIMARY activity    index   ix_ivi_created created_at   8   NULL    25  Using where
1   PRIMARY activity_retail eq_ref  PRIMARY PRIMARY 4   activity.activity_id    1   

虽然它是一个小查询(根据EXPLAIN)，但执行时间很长，并且没有使用EXTRAS区域中的任何内容。

关于如何使其正常工作有什么建议 - 基本上是使用 OR 语句跨两个表的 WHERE 条件？ AND 效果非常好，但这里我们需要 OR。

Answer 1

您似乎非常接近左连接。但是，一旦将条件移动到零售表的 WHERE 子句中，它就会强制进行内部联接。尝试以下操作...

SELECT 
      A.*,
      AR.* 
   from 
      activity A
         LEFT JOIN activity_retail AR
            on A.activity_id = AR.activity_id 
           AND AR.Venue_ID = X
   WHERE 
          A.Venue_ID = X
      OR  NOT AR.Activity_ID IS NULL
   ORDER BY 
      AR.created_at desc 
   LIMIT 
      0, 25

请注意，我将“AR.Venue_ID = X”移至 JOIN 子句中。这样，它仅在辅助表具有“X”时才匹配，否则它将始终为空。

现在，where 子句测试事件表(始终测试记录)的 Venue_ID 或 AR_ActivityID 不为 NULL(这意味着它在表中找到了具有正确地点的记录，因此确实存在可以拉入结果的情况)设置。

希望这能满足您的需求。