can any one help me to optimize this query:
SELECT CONCAT(CCD.CONTACT_FIRST_NAME, ’ ', CCD.CONTACT_LAST_NAME) AS NAME, A.EMAIL_IDS, CCD.UNSUBSCRIBE, IFNULL(CSL.LOG_DATE, ‘’) AS LOG_DATE, CSL.IP_ADDRESS, IF(CSL.BROWSER IS NULL, ‘’, CSL.BROWSER) AS BROWSER, (CASE WHEN (UNSUBSCRIBE = 0 OR UNSUBSCRIBE IS NULL) THEN ‘Opted In’ ELSE (CASE WHEN (UNSUBSCRIBE = 1) THEN ‘Opted Out’ ELSE (CASE WHEN ((UNSUBSCRIBE = 2 OR UNSUBSCRIBE = 3) AND CCD.CONTACT_ID NOT IN (SELECT CONTACT_ID FROM CM_SUBSCRIPTION_MAIL_DATA WHERE IS_MAIL_SENT = ‘Y’)) THEN ‘Opt-In Request not Sent’ ELSE ‘Opt-In Pending’ END) END) END) AS CURR_SUB, (CASE WHEN (SUBSCRIBE_FROM = 0) THEN ‘Opted In’ ELSE (CASE WHEN (SUBSCRIBE_FROM = 1) THEN ‘Opted Out’ ELSE (CASE WHEN (SUBSCRIBE_FROM = 2 OR SUBSCRIBE_FROM = 3) THEN ‘Opt-In Pending’ ELSE ‘’ END) END) END) AS SUB_FROM, SUBSCRIBE_FROM, (CASE WHEN (SUBSCRIBE_TO = 0) THEN ‘Opted In’ ELSE (CASE WHEN (SUBSCRIBE_TO = 1) THEN ‘Opted Out’ ELSE (CASE WHEN (SUBSCRIBE_TO = 2 OR SUBSCRIBE_TO = 3) THEN ‘Opt-In Pending’ ELSE ‘’ END) END) END) AS SUB_TO, SUBSCRIBE_TO FROM CM_CONTACT_DETAILS CCD LEFT JOIN ADDRESS A ON CCD.CONTACT_ID = A.FOREIGN_ID LEFT JOIN CM_SUBSCRIPTION_LOGS CSL ON CSL.CONTACT_ID = CCD.CONTACT_ID WHERE 1 = 1 GROUP BY CSL.LOG_DATE , CCD.CONTACT_ID ORDER BY NAME DESC , LOG_DATE DESC LIMIT 0 , 20
Can you please provide table structure for all involved tables in the query.
mysql> SHOW CREATE TABLE table_name\G
Further, also provide EXPLAIN output for the query.
mysql> EXPLAIN