title: Union All Query takes too long
body: <p>This question have been asked multiple times I am sure, but every case is different.</p>
  
  <p>I have MySQL setup on a strong computer with 2GB RAM, it does not do too much so the computer is sufficient.</p>
  
  <p>The following query has been built as a view :</p>
  
  <pre><code>create view view_orders as
  
  select distinct 
  
      tbl_orders_order.order_date AS sort_col,
      tbl_orders_order.order_id AS order_id,
  _utf8'website' AS src,tbl_order_users.company AS company,
  tbl_order_users.phone AS phone,
  tbl_order_users.full_name AS full_name,
  time_format(tbl_orders_order.order_date,_utf8'%H:%i') AS c_time,
  date_format(tbl_orders_order.order_date,_utf8'%d/%m/%Y') AS c_date,
  tbl_orders_order.comments AS comments,
  tbl_orders_order.tmp_cname AS tmp_cname,
  tbl_orders_order.tmp_pname AS tmp_pname,
  count(tbl_order_docfiles.docfile_id) AS number_of_files,
  (case tbl_orders_order.status when 1 then _utf8'completed' when 2 then _utf8'hc' when 0 then _utf8'not-completed' when 3 then _utf8'hc-canceled' end) AS status,
  
  tbl_orders_order.employee_name AS employee_name,
  tbl_orders_order.status_date AS status_date,
  tbl_orders_order.cancel_reason AS cancel_reason
  
   from 
          tbl_orders_order left join tbl_order_users on tbl_orders_order.user_id = tbl_order_users.user_id 
      left join 
          tbl_order_docfiles on tbl_order_docfiles.order_id = tbl_orders_order.order_id
  
      group by 
          tbl_orders_order.order_id 
  
  
  
  union all
  
  select distinct tbl_h.h_date AS sort_col,
  (case tbl_h.sub_oid when 0 then tbl_h.order_number else concat(tbl_h.order_number,_utf8'-',tbl_h.sub_oid) end) AS order_id,
  (case tbl_h.type when 1 then _utf8'פקס' when 2 then _utf8'email' end) AS src,_utf8'' AS company,
  _utf8'' AS phone,_utf8'' AS full_name,time_format(tbl_h.h_date,_utf8'%H:%i') AS c_time,
  date_format(tbl_h.h_date,_utf8'%d/%m/%Y') AS c_date,_utf8'' AS comments,tbl_h.client_name AS tmp_cname,
  tbl_h.project_name AS tmp_pname,
  tbl_h.quantity AS number_of_files,
  _utf8'completed' AS status,
  tbl_h.computer_name AS employee_name,
  _utf8'' AS status_date,
  _utf8'' AS cancel_reason 
  
  from tbl_h;
  </code></pre>
  
  <p>The query used UNION, than I read an article about UNION ALL and now uses that.</p>
  
  <p>Query alone takes about 3 seconds to execute (UNION took 4.5-5.5 seconds)
  Each part in seperate runs in seconds.</p>
  
  <p>The application does sorting and select on this view, which makes it processing time even larger - about 6 seconds when query is cached, about 12 seconds or more if data has changed.</p>
  
  <p>I see no other way to combine these two results, as both sorted needs to display to the user and I guess something I am doing is wrong.</p>
  
  <p>Of course both tables uses primary keys.</p>
  
  <p>UPDATE!!!!</p>
  
  <p>It didn't help, I got the utf8/case/date_format out of the union query, and removed distincts, now query takes 4 seconds (even longer).
  query without case/date/utf8 (only union) was shortened to 2.3 seconds (0.3 seconds improvement).</p>
  
  <p>create view view_orders as</p>
  
  <pre><code>select *,
      (CASE src
          WHEN 1 THEN
               _utf8'fax' 
          WHEN 2 THEN 
              _utf8'mail' 
          WHEN 3 THEN
              _utf8'website'
      END) AS src,
  
      time_format(order_date,'%H:%i') AS c_time,
      date_format(order_date,'%d/%m/%Y') AS c_date,
  
      (CASE status 
          WHEN 1 THEN 
              _utf8'completed' 
          WHEN 2 THEN
               _utf8'hc handling' 
          WHEN 0 THEN
               _utf8'not completed' 
          WHEN 3 THEN
               _utf8'canceled'
      END) AS status
  
  FROM
  (
  select 
  
      o.order_date AS sort_col,
      o.order_id,
      3 AS src,
  
      u.company,
      u.phone,
      u.full_name,
  
      o.order_date,
  
      o.comments,
      o.tmp_cname,
      o.tmp_pname,
      count(doc.docfile_id) AS number_of_files,
  
      o.status,
  
      o.employee_name,
      o.status_date,
      o.cancel_reason
  
   from 
          tbl_orders_order o
      LEFT JOIN
          tbl_order_users u ON u.user_id = o.user_id
      LEFT JOIN
          tbl_order_docfiles doc ON doc.order_id = o.order_id
  
      GROUP BY
          o.order_id 
  
  union all
  
  select 
  
      h.h_date AS sort_col,
      (case h.sub_oid when 0 then h.order_number else concat(h.order_number,'-',h.sub_oid) end) AS order_id,
      h.type as src,
  
      '' AS company,
      '' AS phone,
      '' AS full_name,
  
      h.h_date,
  
      '' AS comments,
      h.client_name AS tmp_cname,
      h.project_name AS tmp_pname,
      h.quantity AS number_of_files,
      1 AS status,
  
      h.computer_name AS employee_name,
      '' AS status_date,
      '' AS cancel_reason 
  
  from tbl_h h
  </code></pre>
  
  <p>)</p>