MySQL Forums
Forum List  »  Optimizer & Parser

Re: how to optimize this sql?
Posted by: Rick James
Date: July 09, 2016 11:04PM

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.

Options: ReplyQuote


Subject
Views
Written By
Posted
1345
h h
July 07, 2016 07:38PM
930
July 08, 2016 10:59AM
906
h h
July 12, 2016 09:01PM
Re: how to optimize this sql?
834
July 09, 2016 11:04PM
837
h h
July 12, 2016 09:08PM
805
July 14, 2016 12:52PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.