Optimize MySQL query with range and multiple joins

Question

I'm looking to optimize the MySQL query below. Is there a multi column index that would be more successful? I have tried (created_date, rep_id) with no luck. Any suggestions to speed this query up are appreciated.

SELECT
customer.rep_id AS `ID`,
COUNT(*) AS Count,
rep.first_name,
rep.last_name

FROM customer 
INNER JOIN appointment ON customer.id = appointment.customer_id
INNER JOIN rep ON customer.rep_id = rep.user_id
INNER JOIN user ON rep.user_id = user.id

WHERE customer.rep_id != 0
AND customer.saved = 0
AND customer.deleted = 0
AND customer.created_date >= '2017-01-01'
AND customer.created_date < '2017-02-01'
AND appointment.current = 1
AND appointment.realStatus IS NOT NULL
AND (   appointment.realStatus not in('rescheduled','cancelled')
    OR (appointment.closed_by_id IS NULL
     OR customer.rep_id != appointment.closed_by_id)
    )
AND user.knocks = 1
AND user.deleted = 0
GROUP BY customer.rep_id
ORDER BY `Count` DESC
LIMIT 50

Here is the EXPLAIN output:

id: 1
select_type: SIMPLE
table: customer
type: range
possible_keys: PRIMARY,rep_id,created_date  
key: NULL
key_len: NULL
ref: NULL
rows: 354846
Extra: Using where; Using temporary; Using filesort

id: 1
select_type: SIMPLE
table: rep
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: rep.user_id
rows: 1
Extra: Using where

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

Answer 1

Slightly rewritten of query for readability and visual association to other tables in the join..

SELECT
        customer.rep_id AS `ID`,
        COUNT(*) AS Count,
        rep.first_name,
        rep.last_name
    FROM 
        customer 
            INNER JOIN appointment 
                ON customer.id = appointment.customer_id
                AND appointment.current = 1
                AND appointment.realStatus IS NOT NULL
            INNER JOIN rep 
                ON customer.rep_id = rep.user_id
                INNER JOIN user 
                    ON rep.user_id = user.id
                    AND user.knocks = 1
                    AND user.deleted = 0
    WHERE 
            customer.rep_id != 0
        AND customer.saved = 0
        AND customer.deleted = 0
        AND customer.created_date >= '2017-01-01'
        AND customer.created_date < '2017-02-01'
        AND ( appointment.realStatus not in('rescheduled','cancelled') 
            OR (    appointment.closed_by_id IS NULL 
                OR  customer.rep_id != appointment.closed_by_id ))
    GROUP BY 
        customer.rep_id
    ORDER BY 
        `Count` DESC
    LIMIT 
        50

You probably need multiple composite indexes to help the query. Additionally, I shifted some of the query elements to better match where the criteria is applicable (such as appointment and user). This helps to identify a better index option too. That said, I would offer the following indexes on each

table        index
customer     (saved, deleted, created_date, rep_id )
rep          (user_id)
appointment  (customer_id, current, realStatus)