Оптимизировать MySQL запрос с диапазоном и несколькими объединениями

Question

Я ищу, чтобы оптимизировать запрос MySQL ниже. Есть ли многостолбцовый индекс, который был бы более успешным? Я пытался (created_date, rep_id) без удачи. Любые предложения по ускорению этого запроса приветствуются.

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

Здесь EXPLAIN выход:

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

Немного переписан запрос для удобства чтения и визуальной связи с другими таблицами в соединении.

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

Возможно, вам понадобится несколько составных индексов, чтобы помочь запросу. Кроме того, я переместил некоторые элементы запроса, чтобы они лучше соответствовали применимым критериям (например, назначение и пользователь). Это также помогает определить лучший вариант индексации. Тем не менее, я бы предложил следующие индексы на каждом

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

Answer 2

Добавьте подсказку индекса: from customer use index (created_date) inner join ...

(Обратите внимание, что use index не делает то, что говорит; он просто заставляет оптимизатор игнорировать любые индексы, которые вы не упомянули.)

Помимо этого, включение индекса customer_id назначения должно быть включено (customer_id,current) может помочь.

Answer 3

Вам, вероятно, нужен составной индекс - что-то вроде:

создать индекс multi для клиента (id, rep_id, create_date);

MySQL использует только один индекс для каждой таблицы (в зависимости от версии, движка и т. Д.), И если он считает, что существующие индексы не будут полезны, он может их игнорировать.

Я предполагаю, что customer.saved и customer.deleted являются столбцами с несколькими возможными значениями - да / нет, 0/1 и т. Д. Они, как правило, игнорируются механизмами оптимизации запросов.