Having tried many different methods, I can't get my query taking shorter than 40 seconds to run with a dataset of ~11000 rows.
The table is a list of hotel bookings, containing (most relevant) Check-In date and Booking Value
My boss wanted a diagram where the values are summed for every month/year in a special way:
Row 1: Sum of value between Jan 2012 - Jan 2012 Row 2: Sum of value between Jan 2012 - Feb 2012 Row 3: Sum of value between Jan 2012 - Mar 2012 Row 4: Sum of value between Jan 2012 - Apr 2012 ... Row 5: Sum of value between Jan 2012 - Dec 2012
And so on for every year. Basically row 5 in this example would return the summed value for the entire year.
CREATE TABLE IF NOT EXISTS `bookings` ( `DBID` int(10) unsigned NOT NULL AUTO_INCREMENT, `checkin` date NOT NULL, `checkout` date NOT NULL, `total` int(11) NOT NULL, PRIMARY KEY (`DBID`,`checkin`), KEY `total` (`total`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=11171 ;
SELECT YEAR(checkin) as year, CONCAT(UCASE(LEFT(MONTHNAME(checkin), 1)),SUBSTRING(MONTHNAME(checkin), 2)) as month, MONTH(checkin) as monthnum, ( SELECT SUM(total) FROM bookings as book2 WHERE YEAR(book2.checkin) = year AND MONTH(book2.checkin) <= monthnum ) as sum FROM `bookings` GROUP BY YEAR(checkin),MONTH(checkin) ORDER BY YEAR(checkin) ASC, MONTH(checkin) ASC
So the subquery is the magic behind this query, it all produces the exact result I want, but the execution time is unacceptable.
I'm not sure how to optimise this further, I can't use joins since it's on the same table, I've indexed everything neccessary, etc.