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

Answer 2

Another option for you to better optimize your indexes. Leaving your original query the same, just formatting to me brings out the key components to help figure out indexes...

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

indexes to help your query... not just the customer table, but also the appointment, rep and user tables too...

table        index
customer     ( saved, deleted, rep_id, id, created )  although created does not appear required
appointment  ( customer_id, current, `status`, date )
rep          (  user_id, name )
user         (  id, active, deleted )

Some of these indexes are covering indexes that prevent the need to go back to the raw data pages as all the key elements are part of the indexes.