title: how to optimize this sql in mysql
body: <p>i have a sql like this:</p>
  
  <pre><code>select t1.id,t1.type from collect t1 
   where t1.type='1' and t1.status='1' 
      and t1.total&gt;(t1.totalComplate+t1.totalIng)  
      and id not in(
               select tid from collect_log t2 
                where t2.tid=t1.id and t2.bid='1146') 
  limit 1;
  </code></pre>
  
  <p>is is ok, but its performance seems not very good and if i using a order command:</p>
  
  <pre><code>select t1.id,t1.type from collect t1 
   where t1.type='1' and t1.status='1' 
      and t1.total&gt;(t1.totalComplate+t1.totalIng)  
      and id not in(
               select tid from collect_log t2 
                where t2.tid=t1.id and t2.bid='1146') 
  order by t1.id asc 
  limit 1;
  </code></pre>
  
  <p>it become even worse.
  how can i optimize this?</p>
  
  <p>here is explain:</p>
  
  <pre><code>id | select_type        | table | type | possible_keys | key | key_len | ref             | rows | Extra                       |
  +----+--------------------+-------+------+---------------+-----+---------+-----------------+------+-----------------------------+
  |  1 | PRIMARY            | t1    | ref  | i2,i1,i3      | i1  | 4       | const,const     |   99 | Using where; Using filesort |
  |  2 | DEPENDENT SUBQUERY | t2    | ref  | i5            | i5  | 65      | img.t1.id,const |    2 | Using where; Using index 
  </code></pre>