用UNION ALL效率太低了

[gotest]

create view xg_product_out_list as
select gpr_id,gp_id,a.barcode as barcode,company_name as kehu,10000000+ot_id as po_id,is_print,print_time,print_user_name,print_real_name,is_balance,balance_time,balance_user_name,balance_real_name,c.pt_id as pt_id,product_name,item_no,price,unit,a.ct_id as ct_id,c.quantity as shuliang,real_name,a.add_time,a.remark,'扫描' as opt_type from xg_product_out as a,xg_garage_finish as c
join xg_product on xg_product.pt_id=c.pt_id
join xg_client on xg_client.ct_id=c.ct_id
join xg_product_price on xg_product_price.pt_id=c.pt_id and xg_product_price.ct_id=c.ct_id
where a.barcode=c.barcode
UNION ALL
select gpr_id,20000000+mt_id as gp_id,'' as barcode,company_name as kehu,20000000+mt_id as po_id,is_print,print_time,print_user_name,print_real_name,is_balance,balance_time,balance_user_name,balance_real_name,b.pt_id as pt_id,product_name,item_no,price,unit,b.ct_id as ct_id,b.quantity as shuliang,real_name1 as real_name,b.add_time,b.remark,'手工' as opt_type from xg_manual_out as b
join xg_product on xg_product.pt_id=b.pt_id
join xg_client on xg_client.ct_id=b.ct_id
join xg_product_price on xg_product_price.pt_id=b.pt_id and xg_product_price.ct_id=b.ct_id
where is_check = 'Y'
如果分开执行的话
第一个查询语句，1秒都不用，瞬间就完成了（总共记录有5051条）
单二个查询语句，1秒都不用，瞬间就完成了（总共记录有0条）
如果两个查询用UNION ALL连接，就要花10秒钟。
请教各位，有什么好方法优化一下。谢谢！

[rucypli]

不应该啊 不用view直接执行这两条语句呢 看看执行计划

[SQL77]

引用 楼主 gotest 的回复:

create view xg_product_out_list as select gpr_id,gp_id,a.barcode as barcode,company_name as kehu,10000000+ot_id as po_id,is_print,print_time,print_user_name,print_real_name,is_balance,balance_time,balance_user_name,balance_real_name,c.pt_id as pt_id,product_name,item_no,price,unit,a.ct_id as ct_id,c.quantity as shuliang,real_name,a.add_time,a.remark,'扫描' as opt_type from xg_product_out as a,xg_garage_finish as c join xg_product on xg_product.pt_id=c.pt_id join xg_client on xg_client.ct_id=c.ct_id join xg_product_price on xg_product_price.pt_id=c.pt_id and xg_product_price.ct_id=c.ct_id where a.barcode=c.barcode UNION ALL select gpr_id,20000000+mt_id as gp_id,'' as barcode,company_name as kehu,20000000+mt_id as po_id,is_print,print_time,print_user_name,print_real_name,is_balance,balance_time,balance_user_name,balance_real_name,b.pt_id as pt_id,product_name,item_no,price,unit,b.ct_id as ct_id,b.quantity as shuliang,real_name1 as real_name,b.add_time,b.remark,'手工' as opt_type from xg_manual_out as b join xg_product on xg_product.pt_id=b.pt_id join xg_client on xg_client.ct_id=b.ct_id join xg_product_price on xg_product_price.pt_id=b.pt_id and xg_product_price.ct_id=b.ct_id where is_check = 'Y' 如果分开执行的话 第一个查询语句，1秒都不用，瞬间就完成了（总共记录有5051条） 单二个查询语句，1秒都不用，瞬间就完成了（总共记录有0条） 如果两个查询用UNION ALL连接，就要花10秒钟。 请教各位，有什么好方法优化一下。谢谢！

是否有数据类型不一致需要 转换

[gotest]

引用 1 楼 rucypli 的回复:

不应该啊 不用view直接执行这两条语句呢 看看执行计划

不用view直接执行这两条语句也是需要10秒

[gotest]

引用 2 楼 SQL77 的回复:

是否有数据类型不一致需要 转换

我只有一个字段也试过了，比如kehu这个字段，也需要10秒。

[SQL77]

引用 4 楼 gotest 的回复:

[quote=引用 2 楼 SQL77 的回复:] 是否有数据类型不一致需要 转换

我只有一个字段也试过了，比如kehu这个字段，也需要10秒。[/quote] explain 语句 \G;看看。

[gotest]

explain的结果如下： *************************** 1. row *************************** id: 1 select_type: PRIMARY table: a type: ALL possible_keys: NULL key: NULL key_len: NULL ref: NULL rows: 5429 Extra: *************************** 2. row *************************** id: 1 select_type: PRIMARY table: c type: ALL possible_keys: NULL key: NULL key_len: NULL ref: NULL rows: 6789 Extra: Using where; Using join buffer *************************** 3. row *************************** id: 1 select_type: PRIMARY table: xg_product_price type: ref possible_keys: pt_id key: pt_id key_len: 4 ref: xigui_www.c.pt_id rows: 1 Extra: Using where *************************** 4. row *************************** id: 1 select_type: PRIMARY table: xg_client type: eq_ref possible_keys: PRIMARY key: PRIMARY key_len: 4 ref: xigui_www.xg_product_price.ct_id rows: 1 Extra: Using where *************************** 5. row *************************** id: 1 select_type: PRIMARY table: xg_product type: eq_ref possible_keys: PRIMARY key: PRIMARY key_len: 4 ref: xigui_www.xg_product_price.pt_id rows: 1 Extra: Using where *************************** 6. row *************************** id: 2 select_type: UNION table: b type: ALL possible_keys: NULL key: NULL key_len: NULL ref: NULL rows: 1 Extra: Using where *************************** 7. row *************************** id: 2 select_type: UNION table: xg_client type: eq_ref possible_keys: PRIMARY key: PRIMARY key_len: 4 ref: xigui_www.b.ct_id rows: 1 Extra: *************************** 8. row *************************** id: 2 select_type: UNION table: xg_product type: eq_ref possible_keys: PRIMARY key: PRIMARY key_len: 4 ref: xigui_www.b.pt_id rows: 1 Extra: *************************** 9. row *************************** id: 2 select_type: UNION table: xg_product_price type: ref possible_keys: pt_id key: pt_id key_len: 4 ref: xigui_www.b.pt_id rows: 1 Extra: Using where *************************** 10. row *************************** id: NULL select_type: UNION RESULT table: <union1,2> type: ALL possible_keys: NULL key: NULL key_len: NULL ref: NULL rows: NULL Extra: 10 rows in set (0.00 sec) ERROR: No query specified