SQLinfo.ru - Все о MySQL Webew.ru: теория и практика веб-технологий

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

Задавайте вопросы, мы ответим

Вы не зашли.

#1 14.08.2014 19:26:47

ray1992
Участник
Зарегистрирован: 14.08.2014
Сообщений: 2

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

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

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

Вот пример таблицы:
http://i.imgur.com/3eQW1pc.png

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

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

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

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

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

Неактивен

 

#2 15.08.2014 12:08:07

ray1992
Участник
Зарегистрирован: 14.08.2014
Сообщений: 2

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

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

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 сек. Вполне терпимо.

Неактивен

 

Board footer

Работает на PunBB
© Copyright 2002–2008 Rickard Andersson