[MySQL] SQL优化案例1

 SELECT *
 FROM
   ( SELECT a.*,
            e.PAGY_STAT,
            b.brname,
            c.SETL_TYPE,
            c.SETL_SYMBOL,
            c.SETL_CYCLE,
            c.spec_Setl_Day,
            c.SETL_ACCT_NAME,
            c.SETL_ACCT_NO,
            f.MCC_DESC,
            IF (substring(a.mcht_id,1,10)='8201806132',
                                          '1',
                                          '0') AS IS_CUN_LIANG,
               d.audit_dt,
               IF (g.mcht_id IS NOT NULL,
                                '01',
                                '00') AS is_huohu,
                  IF (g.FLAG='2'
                      OR g.FLAG='0',
                                '01',
                                '00') AS is_high_quality
    FROM pbs_mcht_base_info a
    LEFT JOIN ifs_org b ON a.MCHT_ORG_ID = b.brcode
    LEFT JOIN pbs_mcht_contr_info c ON a.mcht_id =c.mcht_id
    LEFT JOIN
      (SELECT MCHT_ID,
              SUBSTR(MAX(LAST_UPD_DATE_TIME),1,8) audit_dt
       FROM pmp_audit_info
       WHERE AUDIT_STATE='01'
       GROUP BY MCHT_ID) d ON d.mcht_id=a.mcht_id
    LEFT JOIN
      (SELECT mcht_id,
              IF(PAGY_MCHT_ID IS NULL
                 OR PAGY_MCHT_ID ='',
                                  '01',
                                  '00') AS PAGY_STAT
       FROM pbs_mcht_base_info) e ON a.mcht_id = e.mcht_id
    LEFT JOIN pbs_mcht_mcc_info f ON a.mcht_Mcc_Code = f.mcc_id
    LEFT JOIN rpt_mcht_active_info g ON a.mcht_id=g.mcht_id
    WHERE 1=1
    ORDER BY a.mcht_id DESC ) aa
 WHERE 1=1 LIMIT 1,
                 10；

 LEFT JOIN
      (SELECT MCHT_ID,
              SUBSTR(MAX(LAST_UPD_DATE_TIME),1,8) audit_dt
       FROM pmp_audit_info
       WHERE AUDIT_STATE='01'
       GROUP BY MCHT_ID) d ON d.mcht_id=a.mcht_id

 LEFT JOIN pmp_audit_info d ON d.mcht_id=a.mcht_id
    AND d.AUDIT_STATE='01'
    AND d.LAST_UPD_DATE_TIME=
      (SELECT max(dd.LAST_UPD_DATE_TIME)
       FROM pmp_audit_info dd
       WHERE d.mcht_id=dd.mcht_id)

 Warning | 1739 | Cannot use ref access on index 'idx_pmp_audit_info_mchtid' due to type or collation conversion on field 'MCHT_ID'

 ALTER TABLE pmp_audit_info MODIFY mcht_id VARCHAR(32) COLLATE utf8_bin;