Mysql query optimization, hope leaders help optimize-CodePudding

SQL and execute the plan as shown in figure,

CodePudding user response:

With several not in, when a user not_enjoy or enjoy data is very big, the query is slow (such as the data on the user's not_enjoy have 1 w),

CodePudding user response:

CSDN already no one?

CodePudding user response:

Not in optimizing
Select * from A where ID not in (select ID from B)
Replace with
Select * from A left join B on Anderson, D=B.I D where B.I D is null

CodePudding user response:

reference csdn_castiel reply: 3/f

not optimized in
Select * from A where ID not in (select ID from B)
Replace with
Select * from A left join B on Anderson, D=B.I D where B.I D is null

In the above SQL, help with the optimization rules change, I write wrong the query results

CodePudding user response:

A key part

CodePudding user response:

.
Left the join enjoy e on lc. User_id=e.o ther_id and e.i srevoke=0 and e.u ser_id=XXX
Left the join not_enjoy ne on lc. User_id=ne. Other_id and ne. Isrevoke=0 and ne.
user_id=XXXLeft the join black_list ne on lc. User_id=b.o ther_id and b.i srevoke=0 and b.u ser_id=XXX
.
The where...
And e.o ther_id is null
And ne. Other_id is null
And b.o ther_id is null

CodePudding user response:

And behind the 1065362 not in... Change

1=1 to remove

Like concat (' d ', '%') for '% d'

Timestampdiff function change

This SQL to write good, can you open it

CodePudding user response:

Here according to your prompt, change the SQL, remove redundant condition only associated not_enjoy enjoy black_list this three tables, but the results should be not to

CodePudding user response:

refer to 6th floor csdn_castiel response:

...
Left the join enjoy e on lc. User_id=e.o ther_id and e.i srevoke=0 and e.u ser_id=XXX
Left the join not_enjoy ne on lc. User_id=ne. Other_id and ne. Isrevoke=0 and ne.
user_id=XXXLeft the join black_list ne on lc. User_id=b.o ther_id and b.i srevoke=0 and b.u ser_id=XXX
.
The where...
And e.o ther_id is null
And ne. Other_id is null
And b.o ther_id is null

The query result seems to be different, not filtered out

CodePudding user response:

The following change which is a step in the right

The where...
Is null and e.i d
And ne. Id is null
And b.i d is null