Как оптимизировать Медленный запрос в MySQL?

Question

[stfed84]

Имеется 3 таблицы
1. Компании wmail_company
2. Заявки wmail_order
3. Счета wmail_comm_bill

Счета связаны с компаниями через заявку (wmail_comm_bill.order_id = wmail_order.id AND wmail_order.company_id = wmail_company.id)

Необходимо выявить 50 компаний у кого больше всего счетов, выполняем запрос:

SELECT wmail_company.id, wmail_company.caption,
(SELECT COUNT(*) FROM wmail_comm_bill, wmail_order WHERE wmail_comm_bill.order_id = wmail_order.id AND wmail_order.company_id = wmail_company.id) AS billcount
FROM wmail_company GROUP BY wmail_company.id ORDER BY billcount DESC LIMIT 0, 50

Он выполняется за 58.211 s - очень долго

EXPLAIN такого вида:
id? select_type? table? partitions? type? possible_keys? key? key_len? ref? rows? Extra?
1 PRIMARY wmail_company NULL ALL NULL NULL NULL NULL 6186 Using filesort
2 DEPENDENT SUBQUERY wmail_comm_bill NULL index order_id order_id 4 NULL 2158 Using index
2 DEPENDENT SUBQUERY wmail_order NULL eq_ref PRIMARY,company_id PRIMARY 4 wmail.wmail_comm_bill.order_id 1 Using where

дополнительные ключи расставлены по wmail_comm_bill.order_id, wmail_order.company_id
основные ключи id есть во всех таблицах

Answer 1

[Сергей Сенкевич]

Попробуйте через JOIN

SELECT c.id, c.caption, COUNT(*) billcount
FROM wmail_company c
    JOIN wmail_order o ON o.company_id = c.id
    JOIN wmail_comm_bill b ON b.order_id = o.id
GROUP BY c.id, c.caption ORDER BY billcount DESC LIMIT 0, 50

все нужные индексы у вас есть.

Comments

[stfed84]

Коллеги спасибо за советы.
Тогда добавлю ... есть еще таблица wmail_comm_bill_item
это товарные позиции счета они связаны с таблицей счетов так wmail_comm_bill_item.com_bill_id = wmail_comm_bill.id
есть стоимость wmail_comm_bill_item.price_total
необходимо в том же запросе узнать не только количество счетов но и их суммы SUM(wmail_comm_bill_item.price_total)

Answer 2

[Samuel_Leonardo]

А замерьте время на таком запросе

SELECT c.id, c.caption, Count(b.id) AS billcount FROM wmail_comm_bill b 
INNER JOIN wmail_order o ON b.order_id = o.id
INNER JOIN wmail_company c ON c.id = o.company_id
GROUP BY c.id
ORDER BY billcount DESC LIMIT 0, 50

Answer 3

[Сергей Протько]

что сразу бросается в глаза:
DEPENDENT SUBQUERY - почему бы не заменить на join?
FROM wmail_comm_bill, wmail_order - опять же join.

select com.id, com.caption, COUNT(com.id) AS billcount
FROM wmail_company com
INNER JOIN wmail_order ord ON ord.company_id = com.id
LEFT JOIN wmail_comm_bill bill ON bill.order_id=ord.id
GROUP BY com.id
ORDER BY billcount DESC
LIMIT 0 50

Answer 4

[KorsaR-ZN]

Эх, пока писал, уже опередили, раз время потратил пусть уж будет :)

SELECT 
	wc.id, 
	wc.caption,
	COUNT(wb.*) AS billcount
INNER JOIN 
	wmail_order AS wo ON wo.company_id = wc.id
INNER JOIN 
	wmail_comm_bill AS wb ON wb.order_id = wo.id
FROM
	wmail_company AS wc
GROUP BY 
	wc.id 
ORDER BY 
	billcount DESC
LIMIT 0, 50

Answer 5

[stfed84]

Коллеги спасибо за советы.
Тогда добавлю ... есть еще таблица wmail_comm_bill_item
это товарные позиции счета они связаны с таблицей счетов так wmail_comm_bill_item.com_bill_id = wmail_comm_bill.id
есть стоимость wmail_comm_bill_item.price_total
необходимо в том же запросе узнать не только количество счетов но и их суммы SUM(wmail_comm_bill_item.price_total)

Comments

[Сергей Сенкевич]

SELECT c.id, c.caption, COUNT(DISTINCT b.id) billcount, SUM(i.price_total) sum_price
FROM wmail_company c
    JOIN wmail_order o ON o.company_id = c.id
    JOIN wmail_comm_bill b ON b.order_id = o.id
    JOIN wmail_comm_bill_item i ON i.comm_bill_id = b.id
GROUP BY c.id, c.caption ORDER BY billcount DESC LIMIT 0, 50

[KorsaR-ZN]

Есть возможность выложить выжимку из базы, тут тестить надо, т.к JOIN, GROUP и SUM вместе работают не совсем правильно, как ожидалось. В моей вопросе - Запрос с подсчетом суммы по объединяющей таблице с between? , есть похожая проблема, к сожалению ни кто не ответил почему так происходит. Может хоть щас ответы будут :)

[stfed84]

Сергей, а как тут же подсчитать количество заявок? count(wmail_order.id)

[Сергей Сенкевич]

@stfed84 вы не упоминали, что надо считать кол-во заявок). Но как-то так:

SELECT c.id, c.caption, COUNT(DISTINCT o.id) ordercount, COUNT(DISTINCT b.id) billcount, SUM(i.price_total) sum_price
FROM wmail_company c
    JOIN wmail_order o ON o.company_id = c.id
    JOIN wmail_comm_bill b ON b.order_id = o.id
    JOIN wmail_comm_bill_item i ON i.comm_bill_id = b.id
GROUP BY c.id, c.caption ORDER BY billcount DESC LIMIT 0, 50

[stfed84]

Тут количество заявок ordercount - это всего заявок или только тех по которым есть счета?

[stfed84]

Т.е. в общем логика подсчета такова: общее количество заявок по компаниям, количество выставленных счетов по этим заявкам, суммы по этим счетам

[stfed84]

сейчас это печально работает так...

SELECT wmail_company.id, wmail_company.caption,

(SELECT COUNT(*) FROM wmail_order WHERE wmail_order.company_id=wmail_company.id) AS ordercount,

(SELECT COUNT(*) FROM wmail_comm_bill, wmail_order WHERE wmail_comm_bill.order_id = wmail_order.id AND wmail_order.company_id=wmail_company.id AND wmail_comm_bill.status = '4') AS billcount,
(SELECT SUM(wmail_comm_bill_item.price_total) FROM wmail_comm_bill_item, wmail_comm_bill, wmail_order WHERE wmail_comm_bill_item.comm_bill_id = wmail_comm_bill.id AND wmail_comm_bill.order_id = wmail_order.id AND wmail_order.company_id=wmail_company.id AND wmail_comm_bill.status = '4') AS billsum

FROM wmail_company

GROUP BY wmail_company.id

ORDER BY billsum DESC

[Сергей Сенкевич]

@stfed84 по идее, ordercount - это кол-во заявок по конкретной компании, billcount - кол-во счетов этой компании, а sum_price - общая сумма по всем этим счетам. Запрос неправильный?

[Сергей Сенкевич]

@stfed84 проверил запрос на похожих таблицах в одном своем проекте - все работает правильно. Ваш запрос из крайнего комментария можно переписать так:

SELECT c.id, c.caption, COUNT(DISTINCT o.id) ordercount, COUNT(DISTINCT b.id) billcount, SUM(i.price_total) billsum
FROM wmail_company c
    JOIN wmail_order o ON o.company_id = c.id
    JOIN wmail_comm_bill b ON b.order_id = o.id
    JOIN wmail_comm_bill_item i ON i.comm_bill_id = b.id AND b.status = 4
GROUP BY c.id, c.caption ORDER BY billsum DESC LIMIT 50

[stfed84]

нет. работает не так... не могу даже понять что он выдает . какие-то произвольные 12 записей не удовлетворяющих ни одному из условий

[stfed84]

если считать только количество заявок
SELECT c.id, c.caption,
COUNT(DISTINCT o.id) ordercount
FROM wmail_company c
JOIN wmail_order o ON o.company_id = c.id
GROUP BY c.id ORDER BY ordercount DESC LIMIT 0, 50;

получаются правильные ответы

если считаем количество заявок и количество счетов
SELECT c.id, c.caption,
COUNT(DISTINCT o.id) ordercount,
COUNT(DISTINCT b.id) billcount
FROM wmail_company c
JOIN wmail_order o ON o.company_id = c.id
JOIN wmail_comm_bill b ON b.order_id = o.id
GROUP BY c.id ORDER BY ordercount DESC LIMIT 0, 50;

получается не то...

может дело в том что во втором случае условия накладываются?
т.е. считается только не общее количество заявок а где есть счета? - это не верно.

[Сергей Сенкевич]

если нужно, чтоб считал не только заявки, у которых есть счета, а все, то тогда так:

SELECT c.id, c.caption, COUNT(DISTINCT o.id) ordercount, COUNT(DISTINCT b.id) billcount, SUM(i.price_total) billsum
FROM wmail_company c
    JOIN wmail_order o ON o.company_id = c.id
    LEFT JOIN wmail_comm_bill b ON b.order_id = o.id
    LEFT JOIN wmail_comm_bill_item i ON i.comm_bill_id = b.id AND b.status = 4
GROUP BY c.id, c.caption ORDER BY billsum DESC LIMIT 50

[stfed84]

сергей спасибо ))) это уже похоже на правду.... осталось разобраться в деталях

SELECT c.id, c.caption,
COUNT(DISTINCT o.id) ordercount,
COUNT(DISTINCT b.id) billcount
FROM wmail_company c
JOIN wmail_order o ON o.company_id = c.id
LEFT JOIN wmail_comm_bill b ON b.order_id = o.id
GROUP BY c.id, c.caption ORDER BY ordercount DESC LIMIT 50
все правильно

но стоит добавить AND b.status = 4 к строке LEFT JOIN wmail_comm_bill b ON b.order_id = o.id - ответ не верный

[stfed84]

если тут сделать так то все работает

SELECT c.id, c.caption, COUNT(DISTINCT o.id) ordercount, COUNT(DISTINCT b.id) billcount
FROM wmail_company c
       JOIN wmail_order o ON o.company_id = c.id
       LEFT JOIN wmail_comm_bill b ON (b.order_id = o.id AND b.status = '4')
GROUP BY c.id, c.caption ORDER BY ordercount DESC LIMIT 50

[stfed84]

если применить этот код на аналогичной задаче по подсчету коммерческих предложений то запрос уходит в никуда, в таблицах wmail_comm_offer и wmail_comm_offer_item в 5 раз больше строк чем wmail_comm_bill и wmail_comm_bill_item

SELECT c.id, c.caption, COUNT(DISTINCT o.id) ordercount, COUNT(DISTINCT b.id) billcount, SUM(i.price_total) billsum
FROM wmail_company c
    JOIN wmail_order o ON o.company_id = c.id
    LEFT JOIN wmail_comm_offer b ON b.order_id = o.id
    LEFT JOIN wmail_comm_offer_item i ON i.comm_offer_id = b.id
GROUP BY c.id, c.caption ORDER BY billsum DESC LIMIT 50

[Сергей Сенкевич]

@stfed84 по b.status = 4 - если добавить это условие, то будут считаться только счета с этим статусом. Если же вам нужно подсчитать кол-во всех счетов, но сумму считать только тех, чей статус равен 4, то запрос будет выглядеть так:

SELECT c.id, c.caption, COUNT(DISTINCT o.id) ordercount, COUNT(DISTINCT b.id) billcount, SUM(IF(b.status = '4', i.price_total, 0)) billsum
FROM wmail_company c
    JOIN wmail_order o ON o.company_id = c.id
    LEFT JOIN wmail_comm_bill b ON b.order_id = o.id
    LEFT JOIN wmail_comm_bill_item i ON i.comm_bill_id = b.id
GROUP BY c.id, c.caption ORDER BY billsum DESC LIMIT 50

по второму запросу - проверьте существование индексов по полям wmail_comm_offer.order_id и wmail_comm_offer_item.comm_offer_id

[stfed84]

b.status = 4 - статус счета "оплачен"
надо было записать так b.status = '4' и работает

вот тут пропустил индекс wmail_comm_offer_item.comm_offer_id
при попытке его поставить с помощью PhpMyAdmin - 504 Gateway Time-out слишком много записей - как поставить индекс?

[Сергей Сенкевич]

@stfed84 через консоль mysql - dev.mysql.com/doc/refman/5.5/en/mysql.html
добавить индекс - dev.mysql.com/doc/refman/5.5/en/alter-table.html

ALTER TABLE wmail_comm_offer_item ADD INDEX(comm_offer_id);

[stfed84]

В общем отдельно два запроса (вычисление количества КП и их сумм, вычисление количества оплаченных счетов и их сумм) работают нормально и вычисляют правильные данные.
Но если их "скрестить" в один (это нужно для инструмента аналитики фильтры и сортировки) значения в графе billsum возрастают многократно

SELECT c.id, c.caption,
COUNT(DISTINCT o.id) ordercount,
COUNT(DISTINCT of.id) offercount,
SUM(ofi.price_total) offersum,
COUNT(DISTINCT b.id) billcount,
SUM(i.price_total) billsum
FROM wmail_company c
    JOIN wmail_order o ON (o.company_id = c.id AND o.datetime > '2014-01-01')

    LEFT JOIN wmail_comm_bill b ON (b.order_id = o.id AND b.status = '4')
    LEFT JOIN wmail_comm_bill_item i ON (i.comm_bill_id = b.id)
    LEFT JOIN wmail_comm_offer of ON (of.order_id = o.id)
    LEFT JOIN wmail_comm_offer_item ofi ON (ofi.comm_offer_id = of.id)

GROUP BY c.id ORDER BY billsum DESC

[Сергей Сенкевич]

эти запросы нельзя объединять таким образом. Используйте для этого UNION - dev.mysql.com/doc/refman/5.5/en/union.html

[stfed84]

UNION сольет разные по сути данные в одни столбцы
или я что-то не понял...

[Сергей Сенкевич]

SELECT id, caption, SUM(ordercount) ordercount, SUM(billcount) billcount, SUM(billsum) billsum, SUM(offercount) offercount, SUM(offersum) offersum 
FROM (
    SELECT c.id, c.caption, ordercount, billcount, billsum, 0, 0 FROM
    -- здесь запрос по счетам
    UNION ALL
    SELECT c.id, c.caption, 0, 0, 0, offercount, offersum FROM
    -- здесь запрос по предложениям
) a
GROUP BY id, caption