var sidebar_align = 'right';
var content_container_margin = parseInt('290px');
var sidebar_width = parseInt('270px');
Unanswered: how to refine NOT IN to improve performance
I have a table like the following which will have more than 50,000 entries.
ID | Name | ParentID
1680 | textP1 | 1699
1686 | text1 | 1680
1687 | text2 | 1680
1699 | textP2 | NULL
1685 | try | NULL
1200 | abc | 680
And i would like to run the following query on it
select ID from TAB where job_name like 'text%' and (ParentID is NULL or ParentID not in(select ID from TAB where name like 'text%')
The result as expect with above set of data is 1699. But on usual the results will be in around 15000+ items.
But as i was using NOT IN it is causing a performance issue. Can anyone let me know how to refine this.
Thanks in Advance
JOIN tab AS par
ON par.id = tab.parentid
AND par.job_name LIKE 'text%'
WHERE tab.job_name LIKE 'text%'
AND par.id IS NULL
Seems to me all you need is
select ID from TAB where job_name like 'text%' and ParentID is NULL
Rudy .. Thanks for the help.. Thanks a lot...