title: Mysql union optimization with subquery
body: <p>My problem is this query: </p>
  
  <pre><code>(SELECT    
       ID1,ID2,ID3,ID4,ID5,ID10,ID11,ID13,ID14,ID454,ID453,
       TIME,TEMP_ID,'ID_AUTO',PREDAJCA,VYTVORIL,MAIL,TEMP_ID_HASH,ID_SEND 
     FROM `load_send_calc` 
     WHERE `TEMP_ID` LIKE '$find%' 
       AND ACTIVE = 1 AND TEMP_ID &gt; 0)
  UNION ALL
  (SELECT 
       ID1,ID2,ID3,ID4,ID5,ID10,ID11,ID13,ID14,ID454,ID453,TIME,'',
       ID_AUTO,'','','','','' 
    FROM `temp` 
    WHERE `ID_AUTO` LIKE '$find%' 
      AND `ID_AUTO` NOT IN (SELECT TEMP_ID 
                              FROM `load_send_calc` 
                              WHERE `load_send_calc`.ACTIVE = 1)
  )
  ORDER BY TIME  DESC LIMIT $limitFrom,$limitTo;
  </code></pre>
  
  <p>There are 18000 records in table <code>load_send_calc</code> and 3000 table <code>temp</code>. The query itself take more than 2 minutes to execute. Is there any way to optimize this time? </p>
  
  <p>I already tried to put order into each subqueries but it didnt help significantly. I am really desperate so I really appreciate any kind of help.</p>
  
  <p>EDIT: 
  Here is EXPLAIN result : </p>
  
  <pre><code>id  select_type table   type    possible_keys   key key_len ref rows    Extra
  1   PRIMARY load_send_calc  ALL NULL    NULL    NULL    NULL    18394   Using where
  2   UNION   temp    ALL NULL    NULL    NULL    NULL    1918    Using where
  3   DEPENDENT SUBQUERY  load_send_calc  ALL NULL    NULL    NULL    NULL    18394   Using where
  NULL    UNION RESULT    &lt;union1,2&gt;  ALL NULL    NULL    NULL    NULL    NULL    Using filesort
  </code></pre>