query with 'NOT IN' condition is too slow

mysql, query-optimization, sql

Solution

how about using `LEFT JOIN`,

SELECT  a.fname, a.lname, a.position 
FROM    employees a
        LEFT JOIN projects b
            ON  a.ID = b.emp_ID AND
                b.id = 'BRS213F-013'
WHERE   b.emp_ID IS NULL

Make sure that `Employees.ID` and `projects.emp_ID` must have keys defined on them to make the performance faster.

To make `Employees.ID` a primary key if it has not implemented yet,

ALTER TABLE Employees ADD CONSTRAINT tb_pk PRIMARY KEY (ID)

To make `projects.emp_ID` a foreign key which reference of `Employees.ID` if it has not implemented yet

ALTER TABLE Projects 
ADD CONSTRAINT tb_fk FOREIGN KEY (EmpID) REFERENCES Employees (ID)

Problem

I have a query that reads about 342 records from one table and checks if the records do not exist in the another table with approximately 32000 records. for this, I have used the 'NOT IN' condition and What is ever the best way to run a query faster with 'NOT IN' condition as bellow that seems like the process wants to take whole my life! ``` SELECT fname,lname,position FROM employees WHERE employees.id NOT IN(select projects.empid where projects.id='BRS213F-013') ``` What am I really supposed to do?

Original source