CODEWITHSUNDEEP
mysqljoinunion
clarkk
clarkk

Reputation: 27689

optimizing joins in union queries

How can you optimize a query with union and joins? Is it possible here to move some of the joins outside the union?

(
    SELECT accounting.id, accounting.time, accounting.subaccount_id AS module_id, accounting.balance_invoice_id AS balance_id, accounting.currency_amount*-1 AS currency_amount, accounting.amount*-1 AS amount, enclosure.enc_id_, enclosure.txt, currency.name AS currency_name, IF(balance_invoice.amount, accounting.currency_amount*-1+SUM(balance_invoice.amount), accounting.currency_amount*-1) AS balance
    FROM accounting
    INNER JOIN enclosure ON enclosure.id=accounting.enc_id
    LEFT JOIN currency ON currency.id=accounting.currency_id
    LEFT JOIN balance_invoice ON balance_invoice.accounting_id=accounting.id
    WHERE accounting.att_booked=1
    GROUP BY accounting.id
)
UNION (
    SELECT accounting.id, accounting.time, accounting.subaccountoff_id AS module_id, accounting.balanceoff_invoice_id AS balance_id, accounting.currency_amount, accounting.amount, enclosure.enc_id_, enclosure.txt, currency.name AS currency_name, IF(balance_invoice.amountoff, accounting.currency_amount+SUM(balance_invoice.amountoff), accounting.currency_amount) AS balance
    FROM accounting
    INNER JOIN enclosure ON enclosure.id=accounting.enc_id
    LEFT JOIN currency ON currency.id=accounting.currency_id
    LEFT JOIN balance_invoice ON balance_invoice.accounting_id=accounting.id
    WHERE accounting.att_booked=1 && accounting.type IN (0,1,2)
    GROUP BY accounting.id
) ORDER BY time DESC

Upvotes: 0

Views: 195

Answers (2)

Erwin Brandstetter
Erwin Brandstetter

Reputation: 656804

This should work without the UNION, too. I used a couple of CASE statements instead. Untested:

SELECT a.id, a.time
      ,CASE WHEN a.type IN (0,1,2) THEN a.subaccountoff_id      ELSE a.subaccount_id      END AS module_id
      ,CASE WHEN a.type IN (0,1,2) THEN a.balanceoff_invoice_id ELSE a.balance_invoice_id END AS balance_id
      ,CASE WHEN a.type IN (0,1,2) THEN a.currency_amount       ELSE a.currency_amount*-1 END AS currency_amount
      ,CASE WHEN a.type IN (0,1,2) THEN a.currency_amount       ELSE a.amount*-1          END AS amount
      ,e.enc_id_, e.txt, c.name AS currency_name
      ,CASE WHEN a.type IN (0,1,2)
            THEN IF (b.amountoff, a.currency_amount + SUM(b.amountoff), a.currency_amount) 
            ELSE IF (b.amount,    a.currency_amount*-1 + SUM(b.amount), a.currency_amount*-1) END AS balance
FROM   accounting a
JOIN   enclosure e ON e.id = a.enc_id
LEFT   JOIN currency c ON c.id = a.currency_id
LEFT   JOIN balance_invoice b ON b.accounting_id = a.id
WHERE  a.att_booked = 1
GROUP  BY a.id
ORDER  BY a.time DESC

Upvotes: 0

Devart
Devart

Reputation: 121922

Something like this -

SELECT
  a.id,
  a.time,
  IF(a.acc_type = 1, a.subaccount_id, subaccountoff_id) AS module_id,
  IF(a.acc_type = 1, a.balance_invoice_id, balanceoff_invoice_id) AS balance_id,
  IF(a.acc_type = 1, a.currency_amount * -1, currency_amount) AS currency_amount,
  IF(a.acc_type = 1, a.amount * -1, a.amount) AS amount,
  e.enc_id_,
  e.txt,
  c.name AS currency_name,
  IF(a.acc_type = 1,
    IF(bi.amount, a.currency_amount * - 1 + SUM(bi.amount), a.currency_amount * -1),
    IF(bi.amountoff, a.currency_amount + SUM(bi.amountoff), a.currency_amount)
  ) AS balance
FROM (
  SELECT 1 acc_type, a1.* FROM accounting a1 WHERE a1.att_booked = 1
    UNION
  SELECT 2 acc_type, a2.* FROM accounting a2 WHERE a2.att_booked=1 AND a2.type IN (0,1,2)
  ) a
INNER JOIN enclosure e
  ON e.id = a.enc_id
LEFT JOIN currency c
  ON c.id = a.currency_id
LEFT JOIN balance_invoice bi
  ON bi.accounting_id = a.id
GROUP BY
  a.id
ORDER BY
  time DESC;

Upvotes: 1

Related Questions