mysql链接和子查询那个快_mysql中关联查询和子查询那个更快

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