MySQL:查询优化
SELECT a.FundIDRecv,a.SubscribeDt, b.FundName,
(
SELECT SUM(c.PricePerWeek)
FROM tbl_Hive c
WHERE c.FundID IN
(
SELECT FundID from tbl_FundStatic
WHERE FundID IN
(
SELECT FundIDSend
FROM tbl_FundSubscriptions
WHERE FundIDRecv = a.FundIDRecv
AND SubscribeDt >= subdate(CURDATE(), INTERVAL weekday(CURDATE()) DAY)
)
AND UserID = '14'
)
) as Price
FROM tbl_FundSubscriptions a, tbl_Hive b
WHERE a.FundIDRecv = b.FundID
AND a.SubscribeDt >= subdate(CURDATE(), INTERVAL weekday(CURDATE()) DAY)
AND a.FundIDRecv IN
(
SELECT FundIDRecv
FROM tbl_FundSubscriptions
WHERE FundIDSend IN (
SELECT FundID
FROM tbl_FundStatic
WHERE UserID = '14'
)
)
GROUP BY FundIDRecv
此查询需要大量时间来获取数据。MySQL:查询优化
如何优化此查询以使其执行并获取结果的速度比它快?
用JOIN
子句替换您的嵌套选择。借此例如:
AND a.FundIDRecv IN
(
SELECT FundIDRecv
FROM tbl_FundSubscriptions
WHERE FundIDSend IN (
SELECT FundID
FROM tbl_FundStatic
WHERE UserID = '14'
)
)
为什么不加入tbl_FundStatic
像这样
FROM tbl_FundSubscriptions a
JOIN tbl_FundStatic s ON (a.FundIDSend = s.FundID)
WHERE s.UserID = '14'
当然外部查询,我不知道这是否仍然是正确的,但它会给你一个主意。此外,您应该避免SELECT
子句本身的嵌套选择。这是更好地从它
我该怎么做?你能提供更多关于如何做到这一点的信息吗?我不知道它 – 2011-03-16 11:05:11
是的,我会尝试。感谢您的输入。 – 2011-03-16 11:08:30
未经测试加入tbl_FundStatic
然后选择字段,但是这会给你一展身手:
SELECT a.fundidrecv,
a.subscribedt,
b.fundname,
SUM(b.priceperweek) AS price
FROM tbl_fundsubscriptions a
JOIN tbl_hive b
ON a.fundidrecv = b.fundid
JOIN tbl_fundsubscriptions fs
ON fs.fundidrecv = a.fundidrecv
JOIN tbl_fundstatic fst
ON fst.fundid = fs.fundidsend
AND fst.userid = '14'
WHERE a.subscribedt >= SUBDATE(Curdate(), INTERVAL Weekday(Curdate()) DAY)
GROUP BY a.fundidrecv
您需要在以下几列添加索引:
- (一个。 fundidrecv,a.subscribedt)
- (b.fundid)
- (fs.fundidrecv)
- (fst.f undid,fst.userid)
我会尽快检查并回复您。 – 2011-03-16 11:25:23
+1努力......我懒得一路走下去:-) – 2011-03-16 11:25:59
@ Pentuim10:感谢您的输入。我感谢你的努力,但SUM(b.priceperweek)显示错误的数据。它没有显示预期的结果。 – 2011-03-16 11:33:33
取决于。你的表和索引是什么?这是一个很大的区别。 – 2011-03-16 11:04:22
指出查询应该做什么。 – Pentium10 2011-03-16 11:12:06
请给出您的查询的数据库结构(表/索引)和执行计划(解释) – oryol 2011-03-16 11:25:59