Under the new one, a great god analysis exists and, in my own test, set up two tables: the employee 10 million data; Employee_department 14 data,

the condition of - looks greatExplain the select t1. * from the employee t1
Where a t1. Id in (select employee_id from employee_department);
Query (1) time-consuming: 0.002 s
Explain the select t1. * from the employee t1 where the exists (
Select 1 from employee_department where employee_id=t1. Id
);
Query (2) time-consuming: 23 s
This view the execution plan to understand, but large table, was entangled with:
- table within large
the condition of theSelect t1. * from employee_department t1 where t1. The employee_id in (
Select the id from the employee
)
The query (3) : 0.001 s
Select a t1. * from employee_department t1 where the exists (
Select 1 from the employee where id=t1. Employee_id
)
Query (4) : 0.001 s
Within the mainstream, and online table big situation, using the exists said look not to understand, MySql 5.7 environment

CodePudding user response:

Table when use exist, because only traverse the appearance of data
But in is query in memory, so to exist in time is also possible,

CodePudding user response:

According to (3) select id from the employee, single, need 4 seconds, but the query (3) as long as 0.001 seconds,
This do not understand the SQL execution logic, wants to explain the great god @ the second floor

CodePudding user response:

Explain the print to see

CodePudding user response:

Don't remember is that version 5.7, sometimes will be automatically optimized for the exists in execution, so verify execution plan

CodePudding user response:

Just look at the execution plan

CodePudding user response:

Try this
SELECT
T1. *
The FROM
The employee t1
The JOIN employee_department t2
ON t2. Employee_id=t1. Id;