我创建了2个可以使用相同功能的查询.它们都包含我想要合并到单个查询中的属性,但我无法做到.
查询1
– 给我我想要的结果.慢(~0.700秒)
查询2
– 给了我很多我忽略并跳过的行.快(~0.005秒)
我的目标是修改QUERY 2以删除除每个项目1之外的所有空价格行.我似乎无法做到这一点,因为他们没有表现出色.这是由于我缺乏对MySQL中索引使用的经验和理解.
查询1
使用设计不佳的子查询,该子查询不允许在包含10k行的tbl_sale(e)中使用索引.
SELECT b.id,b.sv,b.description,der.store_id,f.name,der.price
FROM tbl_watch AS a
LEFT JOIN tbl_item AS b ON a.item_id = b.id
LEFT JOIN (
SELECT c.store_id,d.flyer_id,e.item_id,e.price
FROM tbl_storewatch AS c,tbl_storeflyer AS d
FORCE INDEX ( storebeg_ndx ),tbl_sale AS e
WHERE c.user_id = '$user_id'
AND (
d.store_id = c.store_id
AND d.date_beg = '20121206'
)
AND e.flyer_id = d.flyer_id
) AS der ON a.item_id = der.item_id
LEFT JOIN tbl_store as f ON der.store_id = f.id
WHERE a.user_id = '$user_id'
ORDER BY b.description ASC
这是EXPLAIN for QUERY 1
id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY a ref user_item_ndx user_item_ndx 4 const 30 Using index; Using temporary; Using filesort 1 PRIMARY b eq_ref PRIMARY PRIMARY 4 a.item_id 1 1 PRIMARY <derived2> ALL NULL NULL NULL NULL 300 1 PRIMARY f eq_ref PRIMARY PRIMARY 4 der.store_id 1 2 DERIVED c ref user_ndx user_ndx 4 6 2 DERIVED e ALL NULL NULL NULL NULL 9473 Using join buffer 2 DERIVED d eq_ref storebeg_ndx storebeg_ndx 8 c.store_id 1 Using where
查询2
使用非常有效的所有左连接(ORDER BY除外).索引用于每个连接.此查询返回tbl_watch中每个项目的所有可能匹配项.这是查询：
SELECT b.id,c.store_id,e.price
FROM tbl_watch AS a
LEFT JOIN tbl_item AS b ON a.item_id = b.id
LEFT JOIN tbl_storewatch AS c ON c.user_id = '$user_id'
LEFT JOIN tbl_storeflyer AS d ON d.store_id = c.store_id
AND d.date_beg = '$s_date'
LEFT JOIN tbl_sale AS e ON e.item_id = a.item_id
AND e.flyer_id = d.flyer_id
LEFT JOIN tbl_store as f ON d.store_id = f.id
WHERE a.user_id = '$user_id'
ORDER BY b.description ASC
以下是查询的EXPLAIN：
id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE a ref user_item_ndx user_item_ndx 4 const 6 Using index; Using temporary; Using filesort 1 SIMPLE b eq_ref PRIMARY PRIMARY 4 a.item_id 1 1 SIMPLE c ref user_ndx user_ndx 4 const 2 1 SIMPLE d eq_ref storebeg_ndx,storendx storebeg_ndx 8 c.store_id,const 1 1 SIMPLE e eq_ref itemflyer_ndx itemflyer_ndx 8 a.item_id,d.flyer_id 1 1 SIMPLE f eq_ref PRIMARY PRIMARY 4 d.store_id 1
如何修改QUERY 2(更高效)以便在QUERY 1中使用我需要的行？
谢谢
麦克风
解决方法
我认为这个查询会给你你想要的东西：
select a.id,a.sv,a.description,c.id,c.name,b.price
from
tbl_item a left outer join tbl_sale b on (a.id=b.item_id)
left outer join tbl_storeflyer d on (b.flyer_id=d.flyer_id and d.date_beg = '20120801')
left outer join tbl_store c on (d.store_id = c.id)
left outer join tbl_storewatch x on (c.id = x.store_id)
left outer join tbl_watch y on (a.id = y.item_id);
如果涉及NULL,您可能会有一些左连接.另一种方法是使用一个联合,与MysqL可能会更快：
select a.id,c.id as store_id,b.price
from
tbl_item a,tbl_sale b,tbl_storeflyer d,tbl_store c,tbl_storewatch x,tbl_watch y
where
a.id = b.item_id and
b.flyer_id = d.flyer_id and
d.store_id = c.id and
c.id = x.store_id and
a.id = y.item_id and
d.date_beg = '20120801'
union
select a.id,null as store_id,null as name,null as price
from
tbl_item a
where
a.id not in (select b.item_id from tbl_sale b);
