mysql中关联查询和子查询那个更快-CSDN博客

本文链接：https://blog.csdn.net/sky_xin/article/details/84742296

Navicat 11.0.10版本

结果证明表关联比子查询速度更快

1.子查询：0.287s

SELECT  sw,sh,cx,cy,
    (SELECT buy_url FROM b_product WHERE single_file_id = sys_item_id AND deleted = 0) AS buy_url,
    (SELECT discount_price FROM b_product WHERE single_file_id = sys_item_id AND deleted = 0) AS price,
    (SELECT price FROM b_product WHERE single_file_id = sys_item_id AND deleted = 0) AS old_price,
   	(SELECT id FROM b_product WHERE single_file_id = sys_item_id AND deleted = 0) AS product_id,
		CONCAT('single_',sys_item_id) AS id 
		FROM app_polly_file_item WHERE file_id=829 AND type='sys' AND sys_item_type='single' ORDER BY sys_item_id

2.表关联：0.176s

SELECT  apfi.sw,apfi.sh,apfi.cx,apfi.cy,
     bp.buy_url,
     bp.discount_price AS price,
     bp.price AS old_price,
     bp.id AS product_id,
    
		CONCAT('single_',sys_item_id) AS id 
		FROM app_polly_file_item apfi INNER JOIN b_product bp ON apfi.sys_item_id = bp.single_file_id AND bp.deleted=0

WHERE apfi.file_id=829 AND apfi.type='sys' AND apfi.sys_item_type='single' ORDER BY apfi.sys_item_id

更多sql优化参考：http://www.cnblogs.com/eva_2010/articles/2047558.html