I'm planning on creating a view using this SQL SELECT, but the explain for it shows it's using temporary and using filesort. I can't figure out what indices I need in order to fix this problem. Mostly, I'm wondering why it's using filesort instead of using an index to sort.

Table bn_ms_gt_generaltransaction has 7 million records
Table bn_pd_mp_masterprofile has few hundred records
Table bn_pd_sc_scheme also has few hundred records

Here are create table statements:

    CREATE TABLE `bn_ms_gt_generaltransaction` (
      `GNTRDATE` date NOT NULL,
      `GNTRNUMBER` decimal(10,0) NOT NULL,
      `GNTRSUBNUMBER` decimal(5,0) NOT NULL,
      `ORGACODE` varchar(20) NOT NULL,
      `LOCACODE` varchar(20) NOT NULL,
      `PRODCODE` varchar(20) DEFAULT NULL,
      `SCHEMCODE` varchar(20) DEFAULT NULL,
      `BKMSNUMBER` varchar(50) DEFAULT NULL,
      `TRANCODE` varchar(20) NOT NULL,
      `GNTRVALUEDAT` date NOT NULL,
      `GNTRDRCR` varchar(20) NOT NULL,
      `GLACCODE` varchar(50) NOT NULL,
      `CURRCODE` varchar(20) NOT NULL,
      `GNTRAMTFC` decimal(15,2) NOT NULL,
      `GNTRSTATUS` varchar(2) DEFAULT NULL,
      `TMODCODE` varchar(20) DEFAULT NULL,
      PRIMARY KEY (`ORGACODE`,`LOCACODE`,`GNTRDATE`,`GNTRNUMBER`,`GNTRSUBNUMBER`),
      KEY `CC10921796764` (`TMODCODE`),
      KEY `SQL0407170412326` (`TRANCODE`),
      KEY `SQL0407170412328` (`CURRCODE`),
      KEY `SQL0407170412329` (`ORGACODE`,`GLACCODE`),
      KEY `GNTRDRCR` (`GNTRDRCR`),
      KEY `GLACCODE` (`GLACCODE`),
      KEY `GT_ACC_NUM` (`PRODCODE`,`SCHEMCODE`,`BKMSNUMBER`),
      KEY `GT_PK` (`GNTRDATE`,`GNTRNUMBER`,`GNTRSUBNUMBER`),
      KEY `GT_EFFECTDATE` (`GNTREFFECTDAT`),
      KEY `GT_GL_CODE` (`ORGACODE`,`LOCACODE`,`CURRCODE`,`GLACCODE`),
      KEY `INDEX_GNTRDATE` (`GNTRDATE`),
      KEY `INDEX_GNTRVALUEDAT` (`GNTRVALUEDAT`),
      KEY `GT_VOUCHER` (`GNTRDATE`,`GNTRNUMBER`),
      KEY `REPORT_INDEX_2` (`PRODCODE`,`SCHEMCODE`,`BKMSNUMBER`,`GNTRDATE`,`TRANCODE`,`GLACCODE`,`GNTRSTATUS`,`SGT_REVERSALTAG`,`GNTRAMTFC`),
      KEY `INDEX_LRDL_GROUPBY` (`PRODCODE`,`SCHEMCODE`,`BKMSNUMBER`,`GNTRDATE`,`GNTRVALUEDAT`,`GNTREFFECTDAT`,`GNTRNUMBER`,`TRANCODE`,`TMODCODE`),
      KEY `INDEX_LRDL_GROUPBY_LOOSE` (`PRODCODE`,`SCHEMCODE`,`BKMSNUMBER`,`GNTRDATE`,`GNTRVALUEDAT`,`GNTREFFECTDAT`,`GNTRNUMBER`,`TRANCODE`,`TMODCODE`,`GNTRAMTFC`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8


    CREATE TABLE `bn_pd_mp_masterprofile` (
      `PRODCODE` varchar(20) NOT NULL,
      `ORGACODE` varchar(20) DEFAULT NULL,
      `GLACCODE` varchar(50) DEFAULT NULL,
      `PPC_PRCGCODE` varchar(20) DEFAULT NULL,
      PRIMARY KEY (`PRODCODE`),
      KEY `SQL040126114206120` (`PPC_PRCGCODE`),
      KEY `SQL040126114206101` (`ORGACODE`,`GLACCODE`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8

    CREATE TABLE `bn_pd_sc_scheme` (
      `PRODCODE` varchar(20) NOT NULL,
      `SCHEMCODE` varchar(20) NOT NULL,
      `ORGACODE` varchar(20) DEFAULT NULL,
      `GLACCODE` varchar(50) DEFAULT NULL,
      `CURRCODE` varchar(20) DEFAULT NULL,
      `SCHEPURPOSE` varchar(100) DEFAULT NULL,
      `CODE_PENALTY` varchar(50) DEFAULT NULL,
      PRIMARY KEY (`PRODCODE`,`SCHEMCODE`),
      KEY `SQL040126114217543` (`CURRCODE`),
      KEY `CC1318422668625` (`PST_PSTYCODE`),
      KEY `SQL040126114217560` (`ORGACODE`,`GLACCODE`),
      KEY `CC1205848198000` (`ORGACODE`,`CODE_PENALTY`),
      CONSTRAINT `CC1205848198000` FOREIGN KEY (`ORGACODE`, `CODE_PENALTY`) REFERENCES `pr_gl_ca_account` (`ORGACODE`, `GLACCODE`),
      CONSTRAINT `SQL040126114217543` FOREIGN KEY (`CURRCODE`) REFERENCES `pr_gn_cr_currency` (`CURRCODE`),
      CONSTRAINT `SQL040126114217560` FOREIGN KEY (`ORGACODE`, `GLACCODE`) REFERENCES `pr_gl_ca_account` (`ORGACODE`, `GLACCODE`),
      CONSTRAINT `SQL040126114217562` FOREIGN KEY (`PRODCODE`) REFERENCES `bn_pd_mp_masterprofile` (`PRODCODE`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8

And following are two views select

    SELECT
      `a`.`PRODCODE`      AS `PRODCODE`,
      `a`.`SCHEMCODE`     AS `SCHEMCODE`,
      `a`.`BKMSNUMBER`    AS `BKMSNUMBER`,
      `a`.`GNTRDATE`      AS `GNTRDATE`,
      `a`.`GNTRVALUEDAT`  AS `GNTRVALUEDAT`,
      `a`.`GNTREFFECTDAT` AS `GNTREFFECTDAT`,
      `a`.`GNTRNUMBER`    AS `GNTRNUMBER`,
      `a`.`TRANCODE`      AS `TRANCODE`,
      `a`.`TMODCODE`      AS `TMODCODE`,
      (CASE WHEN ((`a`.`TRANCODE` = '29') AND (`a`.`GNTRDRCR` = 'DR') AND (`a`.`GLACCODE` = `sc`.`GLACCODE`)) THEN COALESCE(`a`.`GNTRAMTFC`,0) END) AS `DISB_AMOUNT`,
      (CASE WHEN ((`a`.`TRANCODE` = '29') AND (`a`.`GNTRDRCR` = 'CR') AND (`a`.`GLACCODE` = `sc`.`GLACCODE`)) THEN COALESCE(`a`.`GNTRAMTFC`,0) END) AS `DISB_AMOUNT_REV`,
      (CASE WHEN ((`a`.`TRANCODE` IN ('30','32')) AND (`a`.`GNTRDRCR` = 'CR') AND (COALESCE(`a`.`GNTRSTATUS`,'P')  'C') AND (`a`.`GLACCODE` = `sc`.`GLACCODE`)) THEN COALESCE(`a`.`GNTRAMTFC`,0) END) AS `PRINCIPAL_AMOUNT`,
      (CASE WHEN ((`a`.`TRANCODE` IN ('30','32')) AND (`a`.`GNTRDRCR` = 'CR') AND (COALESCE(`a`.`GNTRSTATUS`,'P') = 'C') AND (`a`.`GLACCODE` = `sc`.`GLACCODE`)) THEN COALESCE(`a`.`GNTRAMTFC`,0) END) AS `PRINCIPAL_AMOUNT_CANCEL`,
      (CASE WHEN ((`a`.`TRANCODE` IN ('30','32')) AND (`a`.`GNTRDRCR` = 'DR') AND (`a`.`GLACCODE` = `sc`.`GLACCODE`)) THEN COALESCE(`a`.`GNTRAMTFC`,0) END) AS `PRINCIPAL_AMOUNT_REV`,
      (CASE WHEN ((`a`.`TRANCODE` IN ('30','73','32')) AND (`a`.`GNTRDRCR` = 'CR') AND (`a`.`GLACCODE` = `sc`.`SCHEPURPOSE`)) THEN COALESCE(`a`.`GNTRAMTFC`,0) END) AS `MARKUP_AMOUNT`,
      (CASE WHEN ((`a`.`TRANCODE` IN ('30','73','32')) AND (`a`.`GNTRDRCR` = 'DR') AND (`a`.`GLACCODE` = `sc`.`SCHEPURPOSE`)) THEN COALESCE(`a`.`GNTRAMTFC`,0) END) AS `MARKUP_AMOUNT_REV`,
      (CASE WHEN ((`a`.`TRANCODE` IN ('30','55','32')) AND (`a`.`GNTRDRCR` = 'CR') AND (`a`.`GLACCODE` = `sc`.`CODE_PENALTY`)) THEN COALESCE(`a`.`GNTRAMTFC`,0) END) AS `OTHER_AMOUNT`,
      (CASE WHEN ((`a`.`TRANCODE` IN ('30','55','32')) AND (`a`.`GNTRDRCR` = 'DR') AND (`a`.`GLACCODE` = `sc`.`CODE_PENALTY`)) THEN COALESCE(`a`.`GNTRAMTFC`,0) END) AS `OTHER_AMOUNT_REV`
    FROM ((`bn_ms_gt_generaltransaction` `a`
        JOIN `bn_pd_mp_masterprofile` `mp`
          ON (((`mp`.`PRODCODE` = `a`.`PRODCODE`)
               AND (`mp`.`PPC_PRCGCODE` = 'LOAN'))))
       JOIN `bn_pd_sc_scheme` `sc`
         ON (((`a`.`PRODCODE` = `sc`.`PRODCODE`)
              AND (`a`.`SCHEMCODE` = `sc`.`SCHEMCODE`))))
    WHERE ((`a`.`TRANCODE` IN('29','30','55','73','32'))
           AND ((`a`.`GLACCODE` = `sc`.`GLACCODE`)
                 OR (`a`.`GLACCODE` = `sc`.`SCHEPURPOSE`)
                 OR (`a`.`GLACCODE` = `sc`.`CODE_PENALTY`))
           AND ((COALESCE(`a`.`GNTRSTATUS`,'P')  'C')
                 OR ((COALESCE(`a`.`GNTRSTATUS`,'P') = 'C')
                     AND (`a`.`GNTRVALUEDAT` = `a`.`GNTRDATE`)
                     AND (`a`.`GNTRVALUEDAT`  `a`.`GNTREFFECTDAT`))))

    SELECT
      `a`.`DMP_PRODCODE`      AS `DMP_PRODCODE`,
      `a`.`DCS_SCHEMCODE`     AS `DCS_SCHEMCODE`,
      `a`.`MBM_BKMSNUMBER`    AS `MBM_BKMSNUMBER`,
      `a`.`SGT_GNTRDATE`      AS `SGT_GNTRDATE`,
      `a`.`SGT_GNTRVALUEDAT`  AS `SGT_GNTRVALUEDAT`,
      `a`.`SGT_GNTREFFECTDAT` AS `SGT_GNTREFFECTDAT`,
      `a`.`SGT_GNTRNUMBER`    AS `SGT_GNTRNUMBER`,
      `a`.`PTR_TRANCODE`      AS `PTR_TRANCODE`,
      `a`.`PTM_TMODCODE`      AS `PTM_TMODCODE`,
      SUM(COALESCE(`a`.`DISB_AMOUNT`,0)) AS `DISB_AMOUNT`,
      SUM(COALESCE(`a`.`DISB_AMOUNT_REV`,0)) AS `DISB_AMOUNT_REV`,
      (SUM(COALESCE(`a`.`DISB_AMOUNT`,0)) - SUM(COALESCE(`a`.`DISB_AMOUNT_REV`,0))) AS `TOTAL_DISB`,
      SUM(COALESCE(`a`.`PRINCIPAL_AMOUNT`,0)) AS `PRINCIPAL_AMOUNT`,
      SUM(COALESCE(`a`.`PRINCIPAL_AMOUNT_REV`,0)) AS `PRINCIPAL_AMOUNT_REV`,
      SUM(COALESCE(`a`.`PRINCIPAL_AMOUNT_CANCEL`,0)) AS `PRINCIPAL_AMOUNT_CANCEL`,
      (SUM(COALESCE(`a`.`PRINCIPAL_AMOUNT`,0)) - SUM(COALESCE(`a`.`PRINCIPAL_AMOUNT_REV`,0))) AS `TOTAL_PRINCIPAL`,
      SUM(COALESCE(`a`.`MARKUP_AMOUNT`,0)) AS `MARKUP_AMOUNT`,
      SUM(COALESCE(`a`.`MARKUP_AMOUNT_REV`,0)) AS `MARKUP_AMOUNT_REV`,
      (SUM(COALESCE(`a`.`MARKUP_AMOUNT`,0)) - SUM(COALESCE(`a`.`MARKUP_AMOUNT_REV`,0))) AS `TOTAL_MARKUP`,
      SUM(COALESCE(`a`.`OTHER_AMOUNT`,0)) AS `OTHER_AMOUNT`,
      SUM(COALESCE(`a`.`OTHER_AMOUNT_REV`,0)) AS `OTHER_AMOUNT_REV`,
      (SUM(COALESCE(`a`.`OTHER_AMOUNT`,0)) - SUM(COALESCE(`a`.`OTHER_AMOUNT_REV`,0))) AS `TOTAL_OTHER`
    FROM `test_view` `a`
    GROUP BY `a`.`DMP_PRODCODE`,`a`.`DCS_SCHEMCODE`,`a`.`MBM_BKMSNUMBER`,`a`.`SGT_GNTRDATE`,`a`.`SGT_GNTRVALUEDAT`,`a`.`SGT_GNTREFFECTDAT`,`a`.`SGT_GNTRNUMBER`,`a`.`PTR_TRANCODE`,`a`.`PTM_TMODCODE`$$

The second view uses the first.

Output from explain:

    id          select_type     table           type            possible_keys           key         key_len         ref         rows            Extra
    1           PRIMARY                   ALL                                                         3564            
    2           DERIVED         mp          ref         PRIMARY,SQL040126114206120  SQL040126114206120          63          const           3           Using where; Using index; Using temporary; Using filesort
    2           DERIVED         sc          ref           PRIMARY           PRIMARY         62          mp.PRODCODE         2           
    2           DERIVED         a           ref         SQL0407170412326,PCA_GLACCODE,GT_ACC_NUM,REPORT_INDEX_2,INDEX_LRDL_GROUPBY,INDEX_LRDL_GROUPBY_LOOSE         INDEX_LRDL_GROUPBY          126         mp.PRODCODE,sc.SCHEMCODE            594         Using index condition; Using where

Now, the top derived table shows using temporary; using filesort.
Even creating index doesn't help. I tried to create index on group by for loose index but doesn't work. AM I missing something?
I have explored about it and found many workarounds but none fits my example. Some suggest to do aggregation in subquery before join which is not possible as MySQL doesn't allow subquery in FROM clause of VIEW.
Creating another view doesn't work as I need to filter records before summation.
So, can someone help me with fixing the issue.