0

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)
Charlie Davis
  • 27
  • 6
  • It looks like you are trying to get a rolling set of daily inflow/outflow for a given 3 month period, grouped by individual date... Why are you joining from "Today" to "Yesterday -1" (which would actually be a 2 day difference instead of "Yesterday +1" to match the current date. What is your overall INTENTION of output. – DRapp Jan 11 '12 at 00:53
  • Ack, sorry, good point. This query is about as far as my SQL knowledge goes so its getting rough for me at this point. I've got a table of transactions. Date it happened, and amount. Trying to get daily totals out of it for the day's inflow and outflows. The end game is getting every day's beginning balance, inflow, outflow and ending balance. :) – Charlie Davis Jan 11 '12 at 01:06

2 Answers2

3

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.

DRapp
  • 47,638
  • 12
  • 72
  • 142
  • This is fantastic. And its near instant. My jaw is on the ground. – Charlie Davis Jan 11 '12 at 18:18
  • 2
    @CharlieDavis, use of MySQL variables can be VERY VERY POWERFUL... I've done many others in posted questions throughout S/O. – DRapp Jan 11 '12 at 19:39
  • I have one more question DRapp. Hope you get this comment. What if I wanted to have the whole query start with a pre-defined balance, so that all the other days afterwards are based upon that pre-defined balance? – Charlie Davis Jan 16 '12 at 22:24
  • 2
    @CharlieDavis, just change your starting value of "PrevBal := 0.00" part to ex: PrevBal := 338.18 (or whatever the startup value is. ( cant use the at sign more than once in a comment. S/O interprets each as as a return comment to a person and only allows one per comment. – DRapp Jan 16 '12 at 23:19
  • Fantastic. Wish I could give you like 20 points for all this. :) – Charlie Davis Jan 17 '12 at 01:07
  • Well, upchucking on the vote in addition to the checkmark helps. In addition, (and for others too), next to each comment, there's an ability to up vote a comment via an arrow that appears on the left as you mouse-over it... I don't know how many you can do per single comment, but I think you can do like 20 up-vote comments per day (maybe more)... Regardless, glad to help – DRapp Jan 17 '12 at 01:14
0
  1. Turn LEFT JOIN to INNER JOIN.
  2. You would better use something like Memcached instead of MySQL cache.
Roman Newaza
  • 11,405
  • 11
  • 58
  • 89