Goods data, realize the comprehensive sequencing, according to the sales, coupon after the price, time, unit price, commission for an integrated sorting,

SQL statements are as follows:

SELECT * FROM ` itaoke_item ` WHERE ` status `=1 ORDER BY (0.2 + qprice sale_num * * * 0.15 + 0.09 + (coupon_price - qprice) appointed the * 0.16 * 0.4) + create_time desc LIMIT 0
13
I explain, the results are as follows:




Has been used is Using filesort

Data article about a total of 200 w, query the status=1 is a state, and then sorted, could you tell me how to optimize the index, the index have been established,


Pray god help optimize!

CodePudding user response:

The great god for help!

CodePudding user response:

No one,,

CodePudding user response:

Calculation, using the function can't use the index, such as to change a train of thought,
Add a field, for example, computed results, through the field application maintenance, this field is also maintained by the trigger

And you create indexes, I feel there is a problem, is ` status ` beginning, where conditions are ` status `=1, just hit the first index, other waste,

CodePudding user response:

The

reference 3 floor ayzen1988 response:

calculation, using the function can't use indexes, such as to change a way of thinking,
Add a field, for example, computed results, through the field application maintenance, this field is also maintained by the trigger

And you create indexes, I feel there is a problem, is ` status ` beginning, where conditions are ` status `=1, just hit the first index, other waste,

My status is, I just check the status=1 case data, add other index under the status, this kind of circumstance how to add?

CodePudding user response:

Like you is unable to avoid sorting through index, suggest to consider the derivative column, I have wrote a blog post, it is to create a column, storage (0.2 + qprice sale_num * * * 0.15 + 0.09 + (coupon_price - qprice) appointed the * 0.4 * 0.16 + create_time) value, and then build an index on the column,

https://blog.csdn.net/weixin_39004901/article/details/84939502