MySQL Forums
Forum List  »  Optimizer & Parser

how to optimize this sql?
Posted by: h h
Date: July 07, 2016 07:38PM

hi ,all

i have a slow query like this

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;

here is execute plan ,and MySQL convert in to range seek, due to lots of coupon_info_id value in IN clause,it need check a lot of record,it let sort very slow ,how can i speed the query ? any recommendation


*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: legend_account_coupon
type: range
possible_keys: idx_coupid
key: idx_coupid
key_len: 4
ref: NULL
rows: 47310
Extra: Using where; Using filesort
1 row in set (0.00 sec)

Options: ReplyQuote


Subject
Views
Written By
Posted
how to optimize this sql?
1344
h h
July 07, 2016 07:38PM
929
July 08, 2016 10:59AM
904
h h
July 12, 2016 09:01PM
833
July 09, 2016 11:04PM
836
h h
July 12, 2016 09:08PM
804
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.