MySQL 查询效率 - 有更好的方法吗？

Question

我有一个查询，它基本上组合了操作表并按时间顺序从中进行选择，同时保留分页..

有没有更有效/更好的方法来做到这一点？查询需要3秒。不可怕..但我认为还有改进的空间，我会经常使用它..

谢谢!

SELECT 
   `newsletters_subscribers`.`email`,
   `newsletters_subscribers`.`first_name`, 
   `newsletters_subscribers`.`last_name`,
   `newsletters_subscribers`.`id` AS subscriber_id,
    COUNT(DISTINCT newsletters_opens.id) AS opens,
    COUNT(DISTINCT newsletters_clicks.id) AS clicks,
    COUNT(DISTINCT newsletters_forwards.id) AS forwards
FROM `thebookrackqccom_newsletters_subscribers` newsletters_subscribers
  LEFT JOIN 
   `thebookrackqccom_newsletters_opens` newsletters_opens
      ON `newsletters_opens`.`subscriber_id` = `newsletters_subscribers`.`id` 
      AND newsletters_opens.newsletter_id = 1
  LEFT JOIN
   `thebookrackqccom_newsletters_clicks` newsletters_clicks 
      ON `newsletters_clicks`.`subscriber_id` = `newsletters_subscribers`.`id` 
      AND newsletters_clicks.newsletter_id = 1
  LEFT JOIN
   `thebookrackqccom_newsletters_forwards` newsletters_forwards 
      ON `newsletters_forwards`.`subscriber_id` = `newsletters_subscribers`.`id` 
      AND newsletters_forwards.newsletter_id = 1
WHERE
     ( newsletters_opens.id IS NOT NULL 
    OR newsletters_clicks.id IS NOT NULL 
    OR newsletters_forwards.id IS NOT NULL ) 
GROUP BY 
   `newsletters_subscribers`.`id`
ORDER BY 
   `newsletters_subscribers`.`email` ASC
LIMIT 25

Answer 1

您需要的是查询可以使用的索引。三个表中每一个的 (newsletter_id, subscribe_id) 上的复合索引会有所帮助。

您还可以像这样重写查询:

SELECT 
    s.email,
    s.first_name, 
    s.last_name,
    s.id                AS subscriber_id,
    COALESCE(o.opens, 0)    AS opens,
    COALESCE(c.clicks, 0)   AS clicks,
    COALESCE(f.forwards, 0) AS forwards
FROM    thebookrackqccom_newsletters_subscribers AS s
  LEFT JOIN 
    ( SELECT subscriber_id,
             COUNT(*) AS opens
      FROM  thebookrackqccom_newsletters_opens 
      WHERE newsletters_opens.newsletter_id = 1
    ) AS o    ON o.subscriber_id = s.id
  LEFT JOIN 
    ( SELECT subscriber_id,
             COUNT(*) AS clicks
      FROM  thebookrackqccom_newsletters_clicks
      WHERE newsletter_id = 1
    ) AS c    ON c.subscriber_id = s.id
  LEFT JOIN 
    ( SELECT subscriber_id,
             COUNT(*) AS forwards
      FROM  thebookrackqccom_newsletters_forwards
      WHERE newsletter_id = 1
    ) AS f    ON f.subscriber_id = s.id
WHERE ( o.subscriber_id IS NOT NULL 
     OR c.subscriber_id IS NOT NULL 
     OR f.subscriber_id IS NOT NULL ) 
ORDER BY 
    s.email ASC
LIMIT 25