can anyone help me to optimize the where clause-CodePudding

i have a below query it using index but not yet optimized I don't know what is the problem can anyone help me with this ?

select 
  date(mfd.MFD_FITMENT_DATE) as MFD_FITMENT_DATE, 
  count(*) cnt_fitment 
FROM 
  MMHSRP_FITMENT_DATE mfd, 
  CUSTOMER_REGISTRATION_DETAILS crd, 
  EMBOSSING_STATION_MAPPING_DETAILS esmd 
where 
  mfd.MFD_CRD_ID = crd.CRD_ID 
  AND esmd.ESMD_SDM_ID = crd.CRD_SDM_ID 
  AND esmd.ESMD_ESM_ID = '9'
  AND mfd.MFD_STATUS = '0' 
  AND mfd.MFD_FITMENT_DATE >= '2022-10-07' 
  AND mfd.MFD_FITMENT_DATE <= '2022-12-06' 
  AND crd.CRD_VARIFICATION_STATUS IN (1, 2, 4) 
GROUP BY 
  mfd.MFD_FITMENT_DATE 
HAVING 
  COUNT(*) >= '5000' \G

    *************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: esmd
         type: ref
possible_keys: idx_ESMD_SDM_ID,idx_ESMD_ESM_ID
          key: idx_ESMD_ESM_ID
      key_len: 8
          ref: const
         rows: 440
        Extra: Using index condition; Using temporary; Using filesort
*************************** 2. row ***************************
           id: 1
  select_type: SIMPLE
        table: crd
         type: ref
possible_keys: PRIMARY,idx_CRD_SDM_ID,idx_CRD_VARIFICATION_STATUS,idx_crd_sdm_id_verfication_status
          key: idx_crd_sdm_id_verfication_status
      key_len: 4
          ref: celexkeyline.esmd.ESMD_SDM_ID
         rows: 660
        Extra: Using where; Using index
*************************** 3. row ***************************
           id: 1
  select_type: SIMPLE
        table: mfd
         type: ref
possible_keys: MFD_STATUS,idx_MFD_CRD_ID,idx_combo,idx_new,MFD_FITMENT_DATE,idx_CRD_FIT_DATE_STATUS
          key: MFD_STATUS
      key_len: 12
          ref: const,celexkeyline.crd.CRD_ID
         rows: 1
        Extra: Using where; Using index

row 3 Table mfd I have used the force index for idx_CRD_FIT_DATE_STATUS but still the same result taking time.

table structure;

row 1:

PRIMARY KEY (`ESMD_ID`),
  KEY `idx_ESMD_SDM_ID` (`ESMD_SDM_ID`),
  KEY `idx_ESMD_ESM_ID` (`ESMD_ESM_ID`)
) ENGINE=InnoDB AUTO_INCREMENT=14006 DEFAULT CHARSET=latin1

row 2:

 PRIMARY KEY (`CRD_ID`),
  KEY `CRD_APP_ID` (`CRD_APP_ID`),
  KEY `idx_CRD_CMM_ID` (`CRD_CMM_ID`),
  KEY `idx_CRD_SDM_ID` (`CRD_SDM_ID`),
  KEY `idx_CRD_ZM_ID` (`CRD_ZM_ID`),
  KEY `idx_CRD_REGN_NUMBER` (`CRD_REGN_NUMBER`),
  KEY `idx_CRD_MOBILE_NUMBER` (`CRD_MOBILE_NUMBER`),
  KEY `idx_CRD_VARIFICATION_STATUS` (`CRD_VARIFICATION_STATUS`),
  KEY `idx_CRD_CHASSIS_NO` (`CRD_CHASSIS_NO`),
  KEY `idx_CRD_REGN_NUMBER_CRD_ID` (`CRD_REGN_NUMBER`,`CRD_ID`),
  KEY `CRD_FITMENT_DATE` (`CRD_FITMENT_DATE`),
  KEY `idx_crd_sdm_id_verfication_status` (`CRD_SDM_ID`,`CRD_VARIFICATION_STATUS`),
  KEY `idx_CRD_IS_REPLACEMENT` (`CRD_IS_REPLACEMENT`)

row 3:

 PRIMARY KEY (`MFD_ID`),
  KEY `MFD_STATUS` (`MFD_STATUS`,`MFD_CRD_ID`,`MFD_FITMENT_DATE`),
  KEY `idx_MFD_CRD_ID` (`MFD_CRD_ID`),
  KEY `idx_combo` (`MFD_FITMENT_DATE`,`MFD_CRD_ID`,`MFD_STATUS`),
  KEY `idx_new` (`MFD_STATUS`,`MFD_FITMENT_DATE`,`MFD_CRD_ID`),
  KEY `MFD_FITMENT_DATE` (`MFD_FITMENT_DATE`),
  KEY `idx_CRD_FIT_DATE_STATUS` (`MFD_CRD_ID`,`MFD_FITMENT_DATE`,`MFD_STATUS`)
) ENGINE=InnoDB AUTO_INCREMENT=2421779 DEFAULT CHARSET=latin1

The above index all based on cardinality

njdnsfkjndkjfbndksjbfkjdbsjkfbkjsdbfkjbdskjfbsdkjbfkjdsbjkfbkjsdbfkj

CodePudding user response：

You need to stop using "old style joins". This style has not been current for over 30 years and is getting in your way. If I re-write your query to use modern joins it looks like this:

SELECT  
  date(mfd.MFD_FITMENT_DATE) as MFD_FITMENT_DATE, 
  count(*) cnt_fitment 
FROM MMHSRP_FITMENT_DATE mfd
JOIN CUSTOMER_REGISTRATION_DETAILS crd ON mfd.MFD_CRD_ID = crd.CRD_ID
JOIN EMBOSSING_STATION_MAPPING_DETAILS esmd ON esmd.ESMD_SDM_ID = crd.CRD_SDM_ID 
WHERE 
      esmd.ESMD_ESM_ID = '9'
  AND mfd.MFD_STATUS = '0' 
  AND mfd.MFD_FITMENT_DATE >= '2022-10-07' 
  AND mfd.MFD_FITMENT_DATE <= '2022-12-06' 
  AND crd.CRD_VARIFICATION_STATUS IN (1, 2, 4) 
GROUP BY mfd.MFD_FITMENT_DATE 
HAVING COUNT(*) >= '5000'

now we can see two of the filters are on linked tables -- we can move those filters to join statements (something you can't do with old style joins) like this:

SELECT  
  date(mfd.MFD_FITMENT_DATE) as MFD_FITMENT_DATE, 
  count(*) cnt_fitment 
FROM MMHSRP_FITMENT_DATE mfd
JOIN CUSTOMER_REGISTRATION_DETAILS crd ON mfd.MFD_CRD_ID = crd.CRD_ID
                                      AND crd.CRD_VARIFICATION_STATUS IN (1, 2, 4) 
JOIN EMBOSSING_STATION_MAPPING_DETAILS esmd ON esmd.ESMD_SDM_ID = crd.CRD_SDM_ID 
                                           and esmd.ESMD_ESM_ID = '9'
WHERE mfd.MFD_STATUS = '0' 
  AND mfd.MFD_FITMENT_DATE >= '2022-10-07' 
  AND mfd.MFD_FITMENT_DATE <= '2022-12-06' 
GROUP BY mfd.MFD_FITMENT_DATE 
HAVING COUNT(*) >= '5000'

This should be faster.

More talk about old style joins from stackoverflow's own Arron Bernard https://sqlblog.org/2009/10/08/bad-habits-to-kick-using-old-style-joins

Sub-query version as in the comments:

SELECT  
  date(mfd.MFD_FITMENT_DATE) as MFD_FITMENT_DATE, 
  count(*) cnt_fitment 
FROM (
   SELECT MFD_FITMENT_DATE, MFD_CRD_ID, MFD_STATUS 
   FROM MMHSRP_FITMENT_DATE mfd
   WHERE mfd.MFD_FITMENT_DATE >= '2022-10-07' 
     AND mfd.MFD_FITMENT_DATE <= '2022-12-06' 
) AS mfd 
JOIN CUSTOMER_REGISTRATION_DETAILS crd ON mfd.MFD_CRD_ID = crd.CRD_ID
                                      AND crd.CRD_VARIFICATION_STATUS IN (1, 2, 4) 
JOIN EMBOSSING_STATION_MAPPING_DETAILS esmd ON esmd.ESMD_SDM_ID = crd.CRD_SDM_ID 
                                           and esmd.ESMD_ESM_ID = '9'
WHERE mfd.MFD_STATUS = '0' 
GROUP BY mfd.MFD_FITMENT_DATE 
HAVING COUNT(*) >= '5000'