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.re,'扫描' 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.re,'手工' 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秒钟。
请教各位,有什么好方法优化一下。谢谢!
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.re,'扫描' 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.re,'手工' 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秒钟。
请教各位,有什么好方法优化一下。谢谢!
我只有一个字段也试过了,比如kehu这个字段,也需要10秒。
我只有一个字段也试过了,比如kehu这个字段,也需要10秒。explain 语句 \G;看看。
*************************** 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