This question have been asked multiple times I am sure, but every case is different.

I have MySQL setup on a strong computer with 2GB RAM, it does not do too much so the computer is sufficient.

The following query has been built as a view :

```
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;

```

The query used UNION, than I read an article about UNION ALL and now uses that.

Query alone takes about 3 seconds to execute (UNION took 4.5-5.5 seconds)
Each part in seperate runs in seconds.

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.

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.

Of course both tables uses primary keys.

UPDATE!!!!

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).

create view view\_orders as

```
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

```

)