Форум пользователей MySQL

ray1992 · 14.08.2014 19:26:47

Здравствуйте. Есть таблица транзакций. Типы:
Daily Earning - дневной заработок
Hold - временная заморозка суммы
UnHold - разморозка
Chargeback - штраф
Bonus - безвозмездный подарок
PayOut - выплата

Для типов кроме Daily Earning, поле id_daily_earning - это ссылка на какой либо день.
Нужно посчитать баланс, то есть от суммы заработка отнять сумму Hold, прибавить сумму UnHold и т.д.
Проблема в том, что при количестве записей в 100к запрос выполняется 15-20 сек

Вот пример таблицы:

Вот таблица: http://pastebin.com/hFaBMNEM
Вот запрос: http://pastebin.com/XVZHdytE
Вот EXPLAIN:

Можно вставить создание таблицы и запрос сюда: http://sqlfiddle.com/#!2/65a283

Из примера таблицы: надо взять записи с Daily Earning, к каждой из них получить суммы Hold, UnHold и пр. и присоединить. А потом уже сгруппировать по выбранному пользователем полю.

П.С. Группировки могут быть по разным полям. Там есть account_id, date и пр. В зависимости от группировки нужно видеть картину по заработкам (по дням, аккаунтам и пр.)

Отредактированно ray1992 (14.08.2014 19:34:17)

ray1992 · 15.08.2014 12:08:07

В общем самый приемлемый вариант оказался таким:

SELECT 

  t2.id,

  t2.offer_id,  

  SUM(t2.sum_hold) AS sum_hold,

  SUM(t2.sum_unhold) AS sum_unhold,

  SUM(t2.sum_chargeback) AS sum_chargeback,

  SUM(t2.sum_bonus) AS sum_bonus,

  SUM(t2.earning) AS sum_earning

  FROM   

  (

    SELECT

    `t`.`id`,

    `t`.`offer_id`,

    `t`.`amount`,

    SUM(IF(OtherTransactions.type = 'Hold', OtherTransactions.amount, 0)) AS sum_hold,

    SUM(IF(OtherTransactions.type = 'UnHold', OtherTransactions.amount, 0)) AS sum_unhold,

    SUM(IF(OtherTransactions.type = 'Chargeback', OtherTransactions.amount, 0)) AS sum_chargeback,

    SUM(IF(OtherTransactions.type = 'Bonus', OtherTransactions.amount, 0)) AS sum_bonus,

    t.amount AS earning

    FROM `payments_transactions` `t`

    LEFT OUTER JOIN `payments_transactions` `OtherTransactions`

      ON (`OtherTransactions`.`id_daily_earning` = `t`.`id`)

      AND (OtherTransactions.type = 'Hold'

      OR OtherTransactions.type = 'UnHold'

      OR OtherTransactions.type = 'Chargeback'

      OR OtherTransactions.type = 'Bonus')

    WHERE (t.type = "Daily Earning")

    GROUP BY t.id

)

t2

GROUP BY t2.offer_id

ORDER BY t2.id DESC

LIMIT 30

Сначала джойним холды и пр (при JOIN нормально работают индексы), считаем их сумму в группировке по t.id. Как заработок берем не сумму amount, а просто amount. Затем оборачиваем это все в еще один запрос, в котором уже группируем как хотим, снова считаем суммы уже в этой группировке и делаем LIMIT.
На 350к записей отрабатывает за ~0.7 сек. Вполне терпимо.

Форум пользователей MySQL

#1 14.08.2014 19:26:47

Оптимизация запроса

#2 15.08.2014 12:08:07

Re: Оптимизация запроса

Board footer