Slow CREATE TEMPORARY TABLE from SELECT in MySQL

I asked this on StackOverflow, but there are not many hints. So:

SHOW CREATE TABLE cashadv_txn:

CREATE TABLE `cashadv_txn` (
  `txn_id` int(11) NOT NULL AUTO_INCREMENT,
  `cashadv_id` int(11) NOT NULL,
  `txn_type` enum('LI','MI','CI','LD','MD','LDA','SRDA','SRD','NRF') DEFAULT NULL,
  `datetime` datetime NOT NULL,
  `amount` float(20,2) NOT NULL,
  `status` varchar(16) DEFAULT NULL,
  `usaepay_invoice_id` int(11) DEFAULT NULL,
  `link_uid` int(11) DEFAULT NULL,
  `link_date` date DEFAULT NULL,
  `link_txn_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`txn_id`),
  KEY `link_txn_id` (`link_txn_id`),
  KEY `type_date_uid` (`cashadv_id`,`txn_type`,`link_date`,`link_uid`),
  KEY `usaepay_invoice_id` (`usaepay_invoice_id`),
  KEY `idx1` (`cashadv_id`,`link_uid`)
) ENGINE=InnoDB AUTO_INCREMENT=4586015 DEFAULT CHARSET=utf8

relevant temporary table variables. I know this is an overkill, but I can reduce it later. This is just to show that they are high enough:

SHOW GLOBAL VARIABLES LIKE 'max_heap_table_size';

SHOW GLOBAL VARIABLES LIKE 'tmp_table_size' ;

This query takes around 0.8-1 seconds:

SELECT SQL_NO_CACHE
            link_uid AS lender_uid,
            cashadv_id,
            SUM(amount) AS lenderTotalCredit      
         FROM
            cashadv_txn      
         WHERE
            cashadv_txn.txn_type='LDA'      
         GROUP BY
            cashadv_id,
            link_uid;

However, this query (which is in fact the previous one saving results into a temporary table) takes 40 seconds:

CREATE TEMPORARY TABLE IF NOT EXISTS tLenderTotalCredits ENGINE=MEMORY  AS (SELECT
            link_uid AS lender_uid,
            cashadv_id,
            SUM(amount) AS lenderTotalCredit      
         FROM
            cashadv_txn      
         WHERE
            cashadv_txn.txn_type='LDA'      
         GROUP BY
            cashadv_id,
            link_uid
            );

SELECT @@version : 5.6.22-1+deb.sury.org~precise+1

UPDATE:

How much RAM do you have? Do you have 2GB to spare? That is what you are allowing for that MEMORY table.

Remove the parentheses CREATE ... AS ( SELECT ... ).

Add

INDEX(txn_type, cashadv_id, link_uid) -- in that order
INDEX(txn_type, status, cashadv_id, link_uid) -- in that order

It is dangerous to have tmp_table_size = 2G. If multiple connections each needed a tmp table, you could quickly run out of RAM. At that point, mysql gets veeery slow.

This construct optimizes poorly, hence runs slowly:

FROM ( SELECT ... )
JOIN ( SELECT ... ) ON ...

DATE(NOW()) --> CURRENT_DATE()

float(20,2) --> FLOAT or DECIMAL(20,2) (probably 20 is excessive)

Consider changing status varchar(16) DEFAULT NULL, to an ENUM.

StackExchange Database Administrators Q#122898, answer score: 3