The optimization process and recommendations:

1. Avoid Calling Functions With Indexed Columns (query line: 17): When a function is used directly on an indexed column, the database's optimizer won’t be able to use the index. For example, if the column `inactive_group` is indexed, the index won’t be used as it’s wrapped with the function `FIND_IN_SET`. If you can’t find an alternative condition that won’t use a function call, a possible solution is to store the required value in a new indexed column.

The optimized query:

SELECT
        COUNT(crm.id) AS total_record,
        SUM(UPPER(opt_in_marketing) = 'NO') AS unsub_email_count,
        SUM(is_bademail = 1) AS invalid_email_count,
        COUNT(DISTINCT mp.`contact_id`) AS inactive_email_count,
        SUM(is_retired = 1 
        OR is_mice_retired = 1) AS retired_count 
    FROM
        (`tbl_contact_173_230` AS crm) 
    INNER JOIN
        `tbl_mandrill_email_mapping` AS mem 
            ON `mem`.`email` = `crm`.`email_address` 
    LEFT JOIN
        `tbl_master_partition` AS mp 
            ON `mp`.`contact_id` = `crm`.`contact_id` 
            AND crm.`ta_submitted_date` < '2016-10-19' 
            AND FIND_IN_SET(5,
        mp.`inactive_group`) > 0 
    WHERE
        `crm`.`email_address` != '' 
        AND `mem`.`log_id` = 'xxxx' 
        AND `mem`.`open` = 0 
        AND `mem`.`hard_bounce` = 0 
        AND `mem`.`reject` = 0 
        AND `mem`.`spam` = 0 
    ORDER BY
        `crm`.`id` ASC