CODEWITHSUNDEEP
mysql
Peon
Peon

Reputation: 8030

create a better query that sums two tables

I created a query, that collects data from two tables, summs them up and shows the count of the cases and the total summ:

SELECT
    count(ut.id) AS total
    , ( SUM(internal_account) - SUM(( SELECT
            SUM( ub.bill_summs )
        FROM
            u_billing ub
        WHERE
            ub.bill_types = 'correction'
            AND ub.contract_id = ut.contract_id
      )) ) AS summ
FROM  
    u_transactions ut
WHERE
    ut.nulled = 0
    AND ut.type = 'comission'
    AND ut._status = 'not_paid'
    AND DATE( ut.add_timestamp ) = DATE( '2012-05-11' );

but it is really slow. On test cases it gave this result:

+-------+-------+
| total | summ  |
+-------+-------+
|   182 | 15105 |
+-------+-------+
1 row in set (4.13 sec)

It is 4.13 seconds on 182 cases and for only 1 day, but my live server has over 600k cases, so this will be extremely slow.

Any ideas, how I can rewrite the query for better performance?

Solution with remade query(ies):

DELETE FROM tmpContractSums;
INSERT INTO tmpContractSums
SELECT
    ub.contract_id
    , SUM( ub.bill_summs ) AS bill_summs
FROM
    u_billing ub
WHERE
    ub.bill_types = 'correction'
GROUP BY ub.contract_id;

SELECT
    count(ut.id) AS total
    , ( SUM(internal_account) - SUM(bill_summs) )
FROM  
    u_transactions ut
LEFT JOIN tmpContractSums t ON ut.contract_id = t.contract_id
WHERE
    ut.nulled = 0
    AND ut.type = 'comission'
    AND ut._status = 'not_paid'
    AND ut.add_timestamp BETWEEN '2012-05-11 00:00:00' AND '2012-05-11 23:59:59';

Execution time: 500ms

PS: Since I can't drop tables with webuser I just created the table:

CREATE TABLE tmpContractSums AS SELECT contract_id, bill_summs FROM u_billing WHERE 1 = 0;

and the I'm deleting the records. Not as fast as drop, but still way faster then original.

Upvotes: 1

Views: 110

Answers (4)

Tony
Tony

Reputation: 2754

This should speed things up:

SELECT 
   COUNT(ua.id) as total,
   SUM(ua.internal_account - ua.corrections) as 'sum'
FROM (  
    SELECT 
       ut.id,
       ut.internal_account,
       SUM(COALESCE(ub.bill_sums, 0)) AS corrections
    FROM
       u_transactions ut
          LEFT JOIN u_billing ub on ut.contract_id = ub.contract_id
    WHERE 
       ut.nulled = 0 AND
       ut.type = 'comission' AND
       ut._status = 'not_paid' AND
       DATE(ub.add_timestamp) = DATE('2012-05-11') AND 
       ut.bill_types = 'correction'
    GROUP BY
       ut.id
    ) as ua

Edit: corrected add_timestamp table prefix and inserted missing GROUP BY

Upvotes: 0

fancyPants
fancyPants

Reputation: 51908

How about simply using a "temporary" table?

DROP TABLE IF EXISTS tmpContractSums;
CREATE TABLE tmpContractSums AS SELECT contract_id, bill_summs FROM u_billing WHERE 1 = 0;
INSERT INTO tmpContractSums
SELECT
    ub.contract_id
    SUM( ub.bill_summs ) AS bill_summs
FROM
    u_billing ub
WHERE
    ub.bill_types = 'correction'
GROUP BY ub.contract_id;

SELECT
    count(ut.id) AS total
    , ( SUM(internal_account) - COALESCE(bill_summs, 0) )
FROM  
    u_transactions ut
LEFT JOIN tmpContractSums t ON ut.contract_id = t.contract_id
WHERE
    ut.nulled = 0
    AND ut.type = 'comission'
    AND ut._status = 'not_paid'
    AND ut.add_timestamp BETWEEN '2012-05-11' AND '2012-05-11 23:59:59';

This should be faster and if you want you can add indexes to the "temporary table" or make it a table with engine=memory if you have enough space.

Or:

DROP TABLE IF EXISTS tmpContractSums;
CREATE TABLE tmpContractSums AS SELECT contract_id, bill_summs FROM u_billing WHERE 1 = 0;
INSERT INTO tmpContractSums
SELECT
    ub.contract_id
    SUM( ub.bill_summs ) AS bill_summs
FROM
    u_billing ub
WHERE
    ub.bill_types = 'correction'
GROUP BY ub.contract_id;

SELECT
    count(ut.id) AS total
    , ( SUM(internal_account) - (SELECT bill_summs FROM tmpContractSums t WHERE ut.contract_id = t.contract_id ) )
FROM  
    u_transactions ut
WHERE
    ut.nulled = 0
    AND ut.type = 'comission'
    AND ut._status = 'not_paid'
    AND ut.add_timestamp BETWEEN '2012-05-11' AND '2012-05-11 23:59:59';

Upvotes: 1

Hamlet Hakobyan
Hamlet Hakobyan

Reputation: 33381

Isn't it? I mean it must work much better.

SELECT
    count(ut.id) AS total
    , SUM(internal_account - COALESCE(ub.b_summs, 0)) summ
FROM u_transactions ut
    LEFT JOIN 
        (
            SELECT 
                contract_id,
                SUM(bill_summs) b_summs
            FROM u_billing
            WHERE bill_types = 'correction'
            GROUP BY contract_id
        ) AS ub
        ON ub.contract_id = ut.contract_id
WHERE
    ut.nulled = 0
    AND ut.type = 'comission'
    AND ut._status = 'not_paid'
    AND DATE( ut.add_timestamp ) = DATE( '2012-05-11' );

Also, you must avoid using function on WHERE clause applied to table column. If you have index, using function not allow index using. Use this instead:

AND ut.add_timestamp >= DATE( '2012-05-11' ) AND ut.add_timestamp < DATE( '2012-05-12' );

Upvotes: 0

whastupduck
whastupduck

Reputation: 1166

Hope this helps

    SELECT 
        COUNT(A.id) AS total,
        (SUM(A.internal_account) - SUM(COALESCE(B.bill_sums, 0)) AS 'Sum'
    FROM
    u_transactions A
    LEFT JOIN u_billing B on A.contract_id = B.contract_id
    WHERE 
    A.nulled = 0 AND
    A.type = 'comission' AND
    A._status = 'not_paid' AND
    DATE(A.add_timestamp) = DATE('2012-05-11')
    AND B.bill_types = 'correction';

EDIT:

I didn't notice that you sum the results of the subquery.:

SELECT
    count(ut.id) AS total
    , ( SUM(internal_account) - **SUM**(( SELECT
            **SUM**( ub.bill_summs )
        FROM
            u_billing ub
        WHERE
            ub.bill_types = 'correction'
            AND ub.contract_id = ut.contract_id
      )) ) AS summ
FROM  
    u_transactions ut

maybe you can replace the sum with coalesce:

-COALESCE(( SELECT
        **SUM**( ub.bill_summs )
    FROM
        u_billing ub
    WHERE
        ub.bill_types = 'correction'
        AND ub.contract_id = ut.contract_id
  ),0)

Upvotes: 0

Related Questions