MySQL optimization with joins, group by, order by count

Question

The following query takes a whopping 4 seconds to execute and I can't seem to figure out why. I have an index on customer(saved, deleted, created, rep_id) which eliminates a full table scan but it doesn't do much to speed it up...

SELECT 
customer.rep_id AS `ID`,
COUNT(*) AS Count,
rep.name
FROM customer
INNER JOIN appointment ON customer.id = appointment.customer_id
  AND appointment.date >= '2017-05-01'
  AND appointment.date < '2017-06-01'
  AND appointment.current = 1
  AND appointment.`status` = 'completed'
INNER JOIN rep ON customer.rep_id = rep.user_id
INNER JOIN user ON rep.user_id = user.id
  AND user.active = 1
  AND user.deleted = 0
WHERE customer.rep_id != 0
AND customer.saved = 0
AND customer.deleted = 0
GROUP BY customer.rep_id 
ORDER BY `Count` DESC
LIMIT 50

EXPLAIN output:

id  1
select_type SIMPLE
table   customer
type    ref
possible_keys   PRIMARY,rep_id,saved_deleted_created_rep,rep_saved_deleted_created
key saved_deleted_created_rep
key_len 2
ref const,const
rows    162007
Extra   Using where; Using index; Using temporary; Using filesort

id  1
select_type SIMPLE
table   contact
type    ref
possible_keys   user_id
key user_id
key_len 4
ref customer.rep_id
rows    1
Extra   Using index condition

id  1
select_type SIMPLE
table   user
type    eq_ref
possible_keys   PRIMARY
key PRIMARY
key_len 4
ref contact.user_id
rows    1
Extra   Using where

id  1
select_type SIMPLE
table   appointment
type    ref
possible_keys   status,date,customer_id
key customer_id
key_len 4
ref customer.id
rows    1
Extra   Using where

Answer 1

Have you tried narrow your table before you joining them all together ?

SELECT 
    customer.rep_id AS `ID`,
    COUNT(*) AS Count,
    contact.name
FROM 
    (
        SELECT 
            id, rep_id
        FROM 
            customer
            JOIN (
                SELECT 
                    customer_id 
                FROM 
                    appointment
                WHERE 
                    date >= '2017-05-01'
                    AND 
                    appointment.date < '2017-06-01'
                    AND 
                    appointment.current = 1
                    AND 
                    appointment.`status` = 'completed'
            ) AS appointment 
            ON customer.id = appointment.customer_id
        WHERE
            customer.rep_id != 0
            AND 
            customer.saved = 0
            AND 
            customer.deleted = 0
    ) AS customer
    JOIN contact 
        ON customer.rep_id = contact.user_id
    JOIN (
        SELECT
            id
        FROM
            user
        WHERE
            user.active = 1
            AND 
            user.deleted = 0
    ) AS user 
        ON contact.user_id = user.id        
GROUP BY customer.rep_id 
ORDER BY `Count` DESC
LIMIT 50