Reputation: 93

Optimize the query(mysql, php)

I have a query like this..

select slno 
from `invoice_master` 
where slno NOT IN (SELECT DISTINCT Inv_slno from `invoice_refresh`)

I have more than 1,40,000 records in both tables(invoice_master, invoice_refresh)

this query taking hell out of time for execution. :(

this is the link for my explain query

enter image description here

Help me to figure out the alternate query..

Upvotes: 1

Answers (2)

Reputation: 68820

I suggest you to use a LEFT OUTER JOIN :

SELECT m.slno 
FROM `invoice_master` m 
LEFT OUTER JOIN `invoice_refresh` r ON m.slno = r.Inv_slno
WHERE r.Inv_slno IS NULL

Upvotes: 0

Reputation: 13465

Try to remove the NOT IS CLAUSE

Have removed it,and used a LEFT JOIN

select 
slno 

from invoice_master 
LEFT JOIN invoice_refresh on (Inv_slno = slno )
where Inv_slno is null

Upvotes: 1