Skip to main content
added 8 characters in body
Source Link
SELECT
    voc.username AS Medewerker,
    voc.bestvastonbep_count AS Doorgezet,
    IFNULL(count(c.user_id), 0) AS Telefoongesprekken,
    IFNULL(voc.bestvastonbep_count / IFNULL(count(c.user_id), 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 `call` c on
    (c.start BETWEEN '2017-02-04' AND '2017-05-04')
    AND c.duration > 0
    AND c.calltype = 'out'
    AND voc.user_id = c.user_id
GROUP BY voc.user_id;
SELECT
    voc.username AS Medewerker,
    voc.bestvastonbep_count AS Doorgezet,
    IFNULL(count(c.user_id), 0) AS Telefoongesprekken,
    voc.bestvastonbep_count / IFNULL(count(c.user_id), 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 `call` c on
    (c.start BETWEEN '2017-02-04' AND '2017-05-04')
    AND c.duration > 0
    AND c.calltype = 'out'
    AND voc.user_id = c.user_id
GROUP BY voc.user_id;
SELECT
    voc.username AS Medewerker,
    voc.bestvastonbep_count AS Doorgezet,
    IFNULL(count(c.user_id), 0) AS Telefoongesprekken,
    IFNULL(voc.bestvastonbep_count / count(c.user_id), "∞") 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 `call` c on
    (c.start BETWEEN '2017-02-04' AND '2017-05-04')
    AND c.duration > 0
    AND c.calltype = 'out'
    AND voc.user_id = c.user_id
GROUP BY voc.user_id;
added 30 characters in body
Source Link

There is an index on call.user_idcall.user_id as well as call.start. 
But the call table currently has about 32 million rows.

It takes about 7 seconds, depending on the size. Faster would be nice, but this is just about acceptable for my purposes.

There is an index on call.user_id. But the call table currently has about 32 million rows.

There is an index on call.user_id as well as call.start. 
But the call table currently has about 32 million rows.

It takes about 7 seconds, depending on the size. Faster would be nice, but this is just about acceptable for my purposes.

added 409 characters in body
Source Link

Edit in response to RDFozz

The EXPLAIN query gives the following result. enter image description here

The first subquery is very fast. (0.1 s)
The second subquery takes about 6 seconds.
The complete query takes about 57 seconds.

There is an index on call.user_id. But the call table currently has about 32 million rows.

Answer I took Plan A as described by Rick James. The query now looks like the following:

SELECT
    voc.username AS Medewerker,
    voc.bestvastonbep_count AS Doorgezet,
    IFNULL(count(c.user_id), 0) AS Telefoongesprekken,
    voc.bestvastonbep_count / IFNULL(count(c.user_id), 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 `call` c on
    (c.start BETWEEN '2017-02-04' AND '2017-05-04')
    AND c.duration > 0
    AND c.calltype = 'out'
    AND voc.user_id = c.user_id
GROUP BY voc.user_id;

Edit in response to RDFozz

The EXPLAIN query gives the following result. enter image description here

The first subquery is very fast. (0.1 s)
The second subquery takes about 6 seconds.
The complete query takes about 57 seconds.

There is an index on call.user_id. But the call table currently has about 32 million rows.

Answer I took Plan A as described by Rick James. The query now looks like the following:

SELECT
    voc.username AS Medewerker,
    voc.bestvastonbep_count AS Doorgezet,
    IFNULL(count(c.user_id), 0) AS Telefoongesprekken,
    voc.bestvastonbep_count / IFNULL(count(c.user_id), 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 `call` c on
    (c.start BETWEEN '2017-02-04' AND '2017-05-04')
    AND c.duration > 0
    AND c.calltype = 'out'
    AND voc.user_id = c.user_id
GROUP BY voc.user_id;
Source Link
Loading