Re: how to optimize this sql?

[Peter Brawley]

"Using index condition" would be better than "Using where". Is idx_coupid not on coupon_info_id? If not, did you try adding a covering index on coupon_info_id?

It's a big result set. What will you be doing with 47K rows? What performance gain do you get by leaving off the Order By clause? If that effect is large, try adding gmt_modified to the coupon_indo_id index.

For more help we'd need to see the result of Show Create Table legend_account_coupon, table row count, MySQL version, RAM available, nnodb_buffer_pool setting if it's InnoDB.