Skip to main content
1 of 4

Joining subqueries with GROUP is slow

Can anyone help me with the following query?

SELECT
  voc.username AS Medewerker,
  voc.bestvastonbep_count AS Doorgezet,
  IFNULL(cc.call_count, 0) AS Telefoongesprekken,
  voc.bestvastonbep_count / IFNULL(cc.call_count, 0) AS Ratio
FROM (
  SELECT
    u.id AS user_id,
    CONCAT(u.firstname, ' ', u.lastname) AS username,
    COUNT(vo.id) AS bestvastonbep_count
  FROM `bestvastonbep`       vo
  JOIN `best`                b   ON b.id = vo.best_id
  JOIN `vastonbepbeststatus` vos ON b.id = vos.best_id
  JOIN `user`                u   ON u.id = b.user_id
  WHERE vos.type = 2
  AND   vos.status = 2
  AND  (b.date BETWEEN '2017-02-04' AND '2017-05-04')
  GROUP BY u.id
) voc
LEFT JOIN (
  SELECT
    user_id,
    COUNT(id) AS call_count
  FROM `call` c
  WHERE  (c.start BETWEEN '2017-02-04' AND '2017-05-04')
  AND c.duration > 0
  AND c.calltype = 'out'
  GROUP BY c.user_id
) cc
ON voc.user_id = cc.user_id;

It produces the desired result, but it's immensely slow.
I've read questions from other people about similar queries. For them it turned out that joining subqueries wasn't necessary. The difference for me is that I'm using GROUP for both subqueries. Hopefully there's a way and I'm just not seeing it.