Re: how to optimize this sql?

[Rick James]

Please provide SHOW CREATE TABLE

Prettyprint:

    select  id as id, is_deleted as isDeleted, gmt_create as gmtCreate,
            creator as creator, gmt_modified as gmtModified, modifier as modifier,
            shop_id as shopId, coupon_code as couponCode, coupon_source as couponSource,
            effective_date as effectiveDate, expire_date as expireDate,
            used_status as usedStatus, account_id as accountId, coupon_info_id as couponInfoId,
            coupon_type as couponType, coupon_name as couponName,
            suite_id as suiteId, flow_id as flowId, flow_sn as flowSn,
            operator_name as operatorName
        from  legend_account_coupon
        WHERE  is_deleted = 'N'
          and  shop_id = 1201
          and  coupon_info_id in ( 4076 , 4075 , 4074 , 4073 , 4072 ,
                    4071 , 4070 , 4069 , 4068 , 4064 , 4065 , 4066 , 4067 ,
                    4063 , 4062 , 4061 , 4048 , 4047 , 4046 , 4045 , 4044 ,
                    4043 , 4042 , 4041 , 4040 , 4039 , 4038 , 4037 , 4036 
                              )
        order by  gmt_modified DESC;

Add these composite indexes:

INDEX(shop_id, is_deleted, coupon_info_id)
INDEX(shop_id, is_deleted, gmt_modified)

(I don't know which is better; the Optimizer will pick.)

That is not a very long list in the IN clause. A single entry would be like "=", and have other optimization possibilities. More than, say, 10K entries has problems.