DMkHkhmT

1CREATE TABLE `cashadv_txn` (
2  `txn_id` int(11) NOT NULL AUTO_INCREMENT,
3  `cashadv_id` int(11) NOT NULL,
4  `txn_type` enum('LI','MI','CI','LD','MD','LDA','SRDA','SRD','NRF') DEFAULT NULL,
5  `datetime` datetime NOT NULL,
6  `amount` float(20,2) NOT NULL,
7  `status` varchar(16) DEFAULT NULL,
8  `usaepay_invoice_id` int(11) DEFAULT NULL,
9  `link_uid` int(11) DEFAULT NULL,
10  `link_date` date DEFAULT NULL,
11  `link_txn_id` int(11) DEFAULT NULL,
12  PRIMARY KEY (`txn_id`),
13  KEY `link_txn_id` (`link_txn_id`),
14  KEY `type_date_uid` (`cashadv_id`,`txn_type`,`link_date`,`link_uid`),
15  KEY `usaepay_invoice_id` (`usaepay_invoice_id`),
16  KEY `idx1` (`cashadv_id`,`link_uid`)
17) ENGINE=InnoDB AUTO_INCREMENT=4586015 DEFAULT CHARSET=utf8
18	
19SHOW GLOBAL VARIABLES LIKE 'max_heap_table_size';
20	
21SHOW GLOBAL VARIABLES LIKE 'tmp_table_size' ;
22	
23SELECT SQL_NO_CACHE
24            link_uid AS lender_uid,
25            cashadv_id,
26            SUM(amount) AS lenderTotalCredit      
27         FROM
28            cashadv_txn      
29         WHERE
30            cashadv_txn.txn_type='LDA'      
31         GROUP BY
32            cashadv_id,
33            link_uid;
34	
35CREATE TEMPORARY TABLE IF NOT EXISTS tLenderTotalCredits ENGINE=MEMORY  AS (SELECT
36            link_uid AS lender_uid,
37            cashadv_id,
38            SUM(amount) AS lenderTotalCredit      
39         FROM
40            cashadv_txn      
41         WHERE
42            cashadv_txn.txn_type='LDA'      
43         GROUP BY
44            cashadv_id,
45            link_uid
46            );
47	
48SELECT
49         cashadv_id,
50         link_date AS DATE,
51         link_uid AS lender_uid,
52         amount,
53         'Approved' AS STATUS   
54      FROM
55         `cashadv_txn`   
56      WHERE
57         txn_type='LD'     
58         AND STATUS='Approved'   
59      UNION
60      ALL SELECT
61         tLenderTotalCredits.cashadv_id,
62         DATE(NOW()) AS DATE,
63         tLenderTotalCredits.lender_uid,
64         IFNULL(tLenderTotalCredits.lenderTotalCredit,0) - IFNULL(tLenderTotalPay.lenderTotalPay,0) AS amount,
65         'Ready' AS STATUS   
66      FROM
67         ( SELECT
68            link_uid AS lender_uid,
69            cashadv_id,
70            SUM(amount) AS lenderTotalCredit      
71         FROM
72            cashadv_txn      
73         WHERE
74            cashadv_txn.txn_type='LDA'      
75         GROUP BY
76            link_uid,
77            cashadv_id ) tLenderTotalCredits   
78      LEFT JOIN
79         (
80            SELECT
81               cashadv_id,
82               link_uid AS lender_uid,
83               SUM(amount) AS lenderTotalPay      
84            FROM
85               cashadv_txn      
86            WHERE
87               txn_type='LD'        
88               AND STATUS='Approved'      
89            GROUP BY
90               cashadv_id,
91               link_uid 
92         ) tLenderTotalPay 
93            ON tLenderTotalPay.cashadv_id=tLenderTotalCredits.cashadv_id   
94            AND tLenderTotalPay.lender_uid=tLenderTotalCredits.lender_uid -- check if there is current day payment     
95      LEFT JOIN
96         cashadv_txn cashadv_txn3 
97            ON cashadv_txn3.txn_type='LD'   
98            AND cashadv_txn3.cashadv_id=tLenderTotalCredits.cashadv_id   
99            AND cashadv_txn3.link_uid=tLenderTotalCredits.lender_uid   
100            AND cashadv_txn3.link_date = DATE(NOW())   
101            AND cashadv_txn3.status='Approved'   
102      WHERE
103         cashadv_txn3.cashadv_id IS NULL
104	
105INDEX(txn_type, cashadv_id, link_uid) -- in that order
106INDEX(txn_type, status, cashadv_id, link_uid) -- in that order
107	
108FROM ( SELECT ... )
109JOIN ( SELECT ... ) ON ...