mysql - 对联接结果进行排序

Question

运行此查询时:

SELECT
    a.id,
    pub.name AS publisher_name,
    pc.name AS placement_name,
    b.name AS banner_name,
    a.lead_id,
    a.partner_id,
    a.type,
    l.status,
    s.correctness,
    a.landing_page,
    t.name AS tracker_name,
    a.date_view,
    a.date_action

FROM actions AS a
LEFT JOIN publishers AS pub ON a.publisher_id = pub.id
LEFT JOIN placements AS pc ON pc.publisher_id = pub.id
LEFT JOIN banners AS b ON b.campaign_id = a.campaign_id
LEFT JOIN leads l ON
    l.lead_id = a.lead_id
    AND l.created = (
        SELECT MAX(created) from leads l2 where l2.lead_id = l.lead_id
    )
LEFT JOIN statuses AS s ON l.status = s.status
LEFT JOIN trackers AS t ON t.id = a.tracker_id
LIMIT 10

我能够按 actions 表中的每一列进行排序。但是，当我尝试例如 ORDER BY b.name(来自 banners 表，加入 actions.banner_id)或 ORDER BY l.lead_id(如上所示，在更复杂的条件下从 leads 加入)MySQL 正在运行查询很长时间(大多数表有数万条记录)。是否有可能在性能方面按连接的列进行排序？

Answer 1

您应该在要排序的列所在的表上使用内部联接重写查询。

例如，如果您按 actions.banner_id 排序

SELECT ...
FROM actions AS a
  JOIN banners AS b ON b.campaign_id = a.campaign_id
  LEFT JOIN  *rest of the query*

您将获得相同的结果，除非没有足够的横幅可以加入操作以产生总共 10 行。

我猜情况并非如此，否则您不会根据 banner_id 进行排序。