Mysql Query optimization

Question

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?

Shakti Singh · Accepted Answer · 2011-06-23 05:55:32Z

3

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

answered Jun 23, 2011 at 5:55

Shakti Singh

86.7k21 gold badges142 silver badges156 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Avinash Over a year ago

there will be no NULL value for table1_id in table 2

Avinash Over a year ago

my local mysql has gone with this query.. :(

Shakti Singh Over a year ago

@Avinash: you should have indexes on columns

Rakesh Sankar · Accepted Answer · 2011-06-23 06:15:36Z

1

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);

edited Jun 23, 2011 at 6:15

answered Jun 23, 2011 at 5:59

Rakesh Sankar

9,4255 gold badges45 silver badges67 bronze badges

3 Comments

Avinash Over a year ago

When I have tried to give index in second table, I got the following Error: #1089 - Incorrect prefix key; the used key part isn't a string, the used length is longer than the key part, or the storage engine doesn't support unique prefix keys

Avinash Over a year ago

ALTER TABLE sites_check.domaincheckresult ADD INDEX Main Domain Id ( nDomainID ( 10 ) )

Rakesh Sankar Over a year ago

Try this create index table2_table1_id on Table2 (table1_id)

Nedcode · Accepted Answer · 2011-06-23 06:19:54Z

1

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.

answered Jun 23, 2011 at 6:19

Nedcode

3413 silver badges7 bronze badges

Comments

Asaph · Accepted Answer · 2011-06-23 06:03:51Z

0

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);

edited Jun 23, 2011 at 6:03

answered Jun 23, 2011 at 5:58

Asaph

163k25 gold badges204 silver badges204 bronze badges

Collectives™ on Stack Overflow

Mysql Query optimization

4 Answers 4

3 Comments

3 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

3 Comments

3 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related