提高MySQL查询性能-数学重查询 [英] Increasing MySQL Query performance - Math heavy query

问题描述

有人愿意帮助我吗?在具有10000行的MEMORY表上，以下查询大约需要18秒.如果我没有"where"约束，则只需不到一分钟的时间.我已经打开查询缓存以及将其作为准备好的语句来尝试.有什么我可以做的吗?索引还是什么?

Anyone willing to help me with this? The following query takes about 18 seconds on a MEMORY table with 10000 rows. If I don't have the "where" constraint, it takes just under a minute. I've got query caching turned on as well as trying it as a prepared statement. Is there anything I can do? Indexes or what not?

SELECT SQL_CACHE
date(todaydata.postdate) as postdate,
SUM(CASE when todaydata.amount > 0 THEN todaydata.amount ELSE 0 END) AS INFLOW,
SUM(CASE when todaydata.amount < 0 THEN todaydata.amount ELSE 0 END) AS OUTFLOW
FROM invoice as todaydata
LEFT JOIN invoice as yesterdaydata ON todaydata.postdate=DATE_ADD(yesterdaydata.postdate,interval -1 day)
where todaydata.postdate between now() - interval 2 month and now() + interval 1 month
GROUP BY date(todaydata.postdate)

推荐答案

我认为，无论您关注的滚动日期范围如何，它都能为您提供所需的...我已经通过创建自己的发票"进行了测试表中标识了两列.利用@ mySQL变量实际上非常简单，可以在查询中内联使用...唯一的是，现在有了一种真正的方法来知道什么是期初"余额，所以我将初始值设置为初始值为零，然后从中进行调整.

I think this will get you what you want with however a rolling date range you are concerned with... I've tested by creating my own "invoice" table with the two columns identified. It actually was quite simple with the utilization of @ mySQL variables that can be used inline in the query... The only thing is, there is now true way to know what an "opening" balance is, so I've set the initial startup value of zero then adjust from that.

踢球者是"PreAgg"查询，它仅按输入/输出日期本身进行汇总.然后，通过按日期顺序对结果进行排序，@ sql变量会出现.

The kicker is the "PreAgg" query to just aggregate by the date itself of in/out. Then, by ordering that result in date order, the @ sql variable kicks in.

select
      PreAgg.PostDate,
      @PrevBal as BegBal,
      PreAgg.OutFlows,
      PreAgg.InFlows,
      @PrevBal := @PrevBal + PreAgg.OutFlows + PreAgg.InFlows as EndBal
   from 
      ( select
              i.postdate,
              sum( if( i.amount < 0, i.amount, 0 ) ) as OutFlows,
              sum( if( i.amount > 0, i.amount, 0 ) ) as InFlows
           from 
              invoice i
           where
              i.postdate between date_sub( now(), interval 2 month )
                             and date_add( now(), interval 1 month )
           group by
              i.postdate
           order by 
              i.postdate ) as PreAgg,
      ( select @PrevBal := 0.00 ) as SqlVars

但是，即使我给了3个月的窗口期(-2个月，+ 1个月)，我也不认为这真的有道理，因为将来的发布还没有发生……可能还有更多重要的是拥有

However, even though I've given a 3 month window (-2 months, +1 month), I don't think that really makes sense as the future postings will not have happened yet... what may be more important is to just have

       where
          i.postdate > date_sub( now(), interval 3 month )

将从当前日期/时间过去3个月.

which will get the last 3 months from current date/time.

这篇关于提高MySQL查询性能-数学重查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！