Select SUM (fen) SUM from jifen where type='qiandao' and DATE_FORMAT (nl_time, '% % m Y)=DATE_FORMAT (CURDATE (),' % % m Y ') and nl_uid in 
(select the uid from the user where user_type='2') 

Too much # # data query speed too slow, 3 minutes, and you won't come to establish the index of the useless! How to optimize

CodePudding user response:

Select SUM (fen) SUM
The from jifen inner join user ur on nl_uid=ur. Uid and ur. User_type='2'
Where type='qiandao'
And nl_time between DATE_ADD (curdate (), interval - day (curdate ()) + 1 day) and DATE_ADD (curdate () day (curdate ()) + 1, the interval 1 month);
- and DATE_FORMAT (nl_time, '% % m Y)=DATE_FORMAT (CURDATE (),' % % m Y ')
- and nl_uid in (select uid from the user where user_type='2')

CodePudding user response:

I know with correlated subqueries, but how to write, I study, the table A and table B, repeat part, and then under the condition of filter

CodePudding user response:

Don't calculate in conditions like DATE_FORMAT (nl_time, '% % m Y)=DATE_FORMAT (CURDATE (),' % % m Y '), calculate the early date of the month, in between, the subquery can use inner join

CodePudding user response:

The subquery can substitute the exists in, not inner join

CodePudding user response:

Performance problem 1: the time function, can use the above mysql5.7 version, build time function index can be solved, DATE_FORMAT (nl_time, '% % m Y)=DATE_FORMAT (CURDATE (),' % % m Y ')
Performance problem 2: in the subquery, modification for the exists or replace with the join,

CodePudding user response:

The equal sign on the left, do not use function,