CODEWITHSUNDEEP
mysqloptimizationquery-optimization
Avinash
Avinash

Reputation: 6174

Mysql Query optimization

Below is my Table Structure

Table 1

id
name


Table 2

id
table1_id

I want the rows from table 1 which have no reference value in table 2.

Example data:

Table 1
id                name                       
1                  demo
2                  demo2
3                  demo3

Table 2

id                table1_id
1                   1
2                   1
3                   1
4                   3
5                   3

So there is no value in table 2 with table1_id 2. I want id 2 from Table 1.

Below id the query i have tried:

SELECT l.id FROM Table1 l WHERE l.id NOT IN (SELECT DISTINCT(r.id) FROM table2 r);

This is returning a proper result but its taking more than 2 minutes to process.

In table 1 i have 4000 rows and in table 2 I have 40000 rows.

Any optimisation to above query or any alternative solution?

Upvotes: 0

Views: 759

Answers (4)

Nedcode
Nedcode

Reputation: 341

What you are trying to acheive is to find orphan records right?

A join that shows all the records from the first(the left) table and the matching values form the other or nulls for no matches is called a left join. I think a left join will do the same job but it is not going to be any faster. Joins are in general slower.

I found a place where it is all well explained - http://explainextended.com/2009/09/15/not-in-vs-not-exists-vs-left-join-is-null-sql-server/

It does not hurt to try with a join though, and tell us were your results the same as expected.

Upvotes: 1

Rakesh Sankar
Rakesh Sankar

Reputation: 9415

Have an index for Table1.id and Table2.table1_id, then try the following query:

SELECT Table1.id FROM Table1 
WHERE Table1.id NOT IN (SELECT Table2.id FROM Table2 group by Table2.table1_id);

Upvotes: 1

Asaph
Asaph

Reputation: 162761

select t1.id from table1 as t1
    left outer join table2 as t2
    on t2.table1_id = t1.id
    where t2.id is null;

or

select t1.id from table1 as t1
    where not exists (select 1
    from table2 as t2 where t2.table1_id = t1.id);

Upvotes: 0

Shakti Singh
Shakti Singh

Reputation: 86336

SELECT * FROM table1 LEFT JOIN table2
ON table1.id=table2.table1_id
WHERE table2.table1_id IS NULL

Upvotes: 3

Related Questions