2

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.

Improve this question
2
  • Confusion: Where is PPC_PRCGCODE defined ?
    – RolandoMySQLDBA
    Commented Jan 6, 2015 at 21:01
  • @RolandoMySQLDBA I have updated the product table with missing column. Secondly, Even if I execute the above two queries by making first as subquery of the second. Still I can see temporary/filesort. So, Can we remove that from it because I have rewritten the view as single query?
    – Nabeel
    Commented Jan 7, 2015 at 4:56

1 Answer 1

Reset to default
1

OBSERVATION #1

The MySQL Documentation gives you the Restrictions on Views

  • It is not possible to create an index on a view.
  • Indexes can be used for views processed using the merge algorithm. However, a view that is processed with the temptable algorithm is unable to take advantage of indexes on its underlying tables (although indexes can be used during generation of the temporary tables).

Subqueries cannot be used in the FROM clause of a view.

Therefore

  • The result set of a View would be some temp table used for row-by-row retrieval
  • There is no existing mechanism to index the result set coming from a view

Creating indexes only help the underlying base tables within the JOIN a the View, not the View itself.

OBSERVATION #2

You mentioned this

Some suggest to do aggregation in subquery before join which is not possible as MySQL doesn't allow subquery in FROM clause.

That's not true. I have a post in StackOverflow (Fetching a Single Row from Join Table) that clearly demonstrates doing an aggregation in a subquery followed by all kinds of JOINs to that subquery.

Improve this answer
1
  • Thanks for your reply. Sorry I didn't mention that its about Views i.e. we cannot use subquery in FROM clause of views. I am updating question with correction and PPC_PRCGCODE column.
    – Nabeel
    Commented Jan 7, 2015 at 4:43

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.