sql - Increasing MySQL Query performance - Math heavy query

Question

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)

Answer 1

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.

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

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 )

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