Mysql - Not In takes Time when nested select query is dynamic but not if it's constant
<p>T1 contains about 30 million rows,
T2 contains about 100k rows</p>

<pre><code>select a from T2 gives ('a1','a2','a3',...); (1 lakh rows)
</code></pre>

<p>When I use 100k constant values directly inside the in block the query returns result in 80 millisec. But, when I use nested select in the query, it takes like forever.</p>

<pre><code>select a,b from T1 where a in ('a1','a2','a3', ...); (Constant Values inside in block)  
select a,b from T1 where a in (select a from T2); (Query instead of values)
</code></pre>

<p>Any Idea why is it happening? Also is there a better way to do so?
Since T1 contains 30 million rows, Left Join also takes a lot of time.</p>

<p>My Actual Query is :    </p>

<pre><code>select a,b from t1 where (a,b) not in (select a,b from t2) and a in (select a from t1); 
</code></pre>