Tim Truman wrote:
Query:
SELECT count(*) as count FROM ( SELECT *
                FROM transaction t, merchant m
WHERE t.merchant_id = m.id AND m.id = 198
                        AND t.transaction_date >= '20050101'
                        AND t.transaction_date <= '20060925'
                        AND credit_card_no LIKE '1111%111'

        UNION ALL
        SELECT *
                FROM transaction t, merchant m
                WHERE t.merchant_id = m.id
                        AND m.parent_merchant_id = 198
                        AND t.transaction_date >= '20050101'
                        AND t.transaction_date <= '20060925'
                        AND credit_card_no LIKE '1111%111'
) AS foobar


Actually, I think the best course of action is to rewrite the query to a faster alternative. What you can try is:
SELECT SUM(count) AS count FROM
(
        SELECT count(*) AS count
                FROM transaction t, merchant m
                WHERE t.merchant_id = m.id
                        AND m.id = 198
                        AND t.transaction_date >= '20050101'
                        AND t.transaction_date <= '20060925'
                        AND credit_card_no LIKE '1111%111'

        UNION ALL
        SELECT count(*) AS count
                FROM transaction t, merchant m
                WHERE t.merchant_id = m.id
                        AND m.parent_merchant_id = 198
                        AND t.transaction_date >= '20050101'
                        AND t.transaction_date <= '20060925'
                        AND credit_card_no LIKE '1111%111'
) AS foobar;


The next optimization is to merge the 2 subqueries into one. If you schema is such that m.id can not be the same as m.parent_merchant_id I think your query can be reduced to:
SELECT count(*) AS count
        FROM transaction t, merchant m
        WHERE t.merchant_id = m.id
                AND
                (
                        m.id = 198
                        OR
                        m.parent_merchant_id = 198
                )
                AND t.transaction_date >= '20050101'
                AND t.transaction_date <= '20060925'
                AND credit_card_no LIKE '1111%111'


If m.id can be the same as m.parent_merchant_id you need something like:
SELECT SUM(
        CASE WHEN m.id = m.parent_merchant_id THEN 2 ELSE 1 END
        ) AS count
        FROM transaction t, merchant m
        WHERE t.merchant_id = m.id
                AND
                (
                        m.id = 198
                        OR
                        m.parent_merchant_id = 198
                )
                AND t.transaction_date >= '20050101'
                AND t.transaction_date <= '20060925'
                AND credit_card_no LIKE '1111%111'

Jochem

---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
      choose an index scan if your joining column's datatypes do not
      match

Reply via email to