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

Answer 2

尝试这个查询，我希望您能获得更好的执行时间

查询

SELECT 
`newsletters_subscribers`.`email`,
`newsletters_subscribers`.`first_name`, 
`newsletters_subscribers`.`last_name`,
`newsletters_subscribers`.`id` AS subscriber_id,
@nopen := coalesce( N_OPEN.NOPENIDCOUNT, 000000 ) as opens,
@nclick := coalesce( N_CLICK.NCLICKIDCOUNT, 000000 ) as clicks,
@nfwd := coalesce( N_FWD.NFWDIDCOUNT, 000000 ) as forwards

FROM 
(select @nopen := 0,@nclick := 0,@nfwd :=0) sqlvars,

`thebookrackqccom_newsletters_subscribers` AS newsletters_subscribers

LEFT JOIN (SELECT `newsletters_opens`.`subscriber_id`,
COUNT(newsletters_opens.id) AS NOPENIDCOUNT 
FROM `thebookrackqccom_newsletters_opens` AS  newsletters_opens 
WHERE newsletters_opens.newsletter_id = 1) AS N_OPEN 
ON N_OPEN.subscriber_id = `newsletters_subscribers`.`id` 


LEFT JOIN (SELECT `newsletters_clicks`.`subscriber_id`,
COUNT(newsletters_clicks.id) AS NCLICKIDCOUNT
FROM `thebookrackqccom_newsletters_clicks` AS  newsletters_clicks 
WHERE newsletters_clicks.newsletter_id = 1) AS N_CLICK
ON N_CLICK.subscriber_id = `newsletters_subscribers`.`id`


LEFT JOIN (SELECT `newsletters_forwards`.`subscriber_id`,
COUNT(newsletters_forwards.id) AS NFWDIDCOUNT
FROM `thebookrackqccom_newsletters_forwards` AS  newsletters_forwards 
WHERE newsletters_forwards.newsletter_id = 1) AS N_FWD
ON N_FWD.subscriber_id = `newsletters_subscribers`.`id` 

GROUP BY `newsletters_subscribers`.`id`
ORDER BY `newsletters_subscribers`.`email` ASC
LIMIT 25