2

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.

Table:

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 ;

My query:

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.

1

Try rewriting the sub-query as a join:

SELECT YEAR(b1.checkin) as year
     , MONTH(b1.checkin) as monthnum,
     , SUM(b2.total)
FROM bookings as b1
JOIN bookings as b2
    ON YEAR(b2.checkin) = YEAR(b1.checkin) 
   AND MONTH(b2.checkin) <= MONTH(b1.monthnum)
GROUP BY YEAR(b1.checkin),MONTH(b1.checkin)
ORDER BY YEAR(b2.checkin) ASC, MONTH(b2.checkin) ASC

I also removed CONCAT(UCASE(LEFT(MONTHNAME(checkin), 1)),SUBSTRING(MONTHNAME(checkin), 2)) as month, I don't think that will matter but it might be beneficial to do this once, and add it to the report in the application-layer.

You don't mention version of MySQL, but later versions have generated columns. You can try generating columns for YEAR and MONTH:

CREATE TABLE IF NOT EXISTS bookings 
( DBID int unsigned NOT NULL AUTO_INCREMENT
, checkin date NOT NULL
, checkout date NOT NULL
, total int NOT NULL

, checkin_year SMALLINT as (YEAR(checkin))
, checkin_month SMALLINT as (MONTH(checkin)) 

,    PRIMARY KEY (DBID, checkin)
,    KEY total (total)

,    KEY (checkin_year, checkin_month)

) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=11171 ;

and then use those columns in the query:

SELECT b1.checkin_year
     , b1.checkin_month,
     , SUM(b2.total)
FROM bookings as b1
JOIN bookings as b2
    ON b2.checkin_year = b1.checkin_year 
   AND b2.checkin_month <= b1.checkin_month
GROUP BY b1.checkin_year, b1.checkin_month
ORDER BY b1.checkin_year, b1.checkin_month

BTW, what is the purpose of the index on TOTAL?

0

If you had an indexed date dimension table, this would work much faster. They are quite useful for solving a number of reporting issues. A very simple example (your dimension can be as robust or simple as you require) would be:

CREATE TABLE month_dimension (Month INT, Year INT, First_Day DATE)

INSERT INTO month_dimension VALUES (1, 2015, '1/1/2015'), (2, 2015, '2/1/2015')...

SELECT a.year AS year,
       CONCAT(UCASE(LEFT(MONTHNAME(a.First_Day), 1)),SUBSTRING(MONTHNAME(a.First_Day), 2)) AS month,
       a.month AS monthnum,
       SUM(b.total) AS sum
       FROM month_dimension a
       LEFT JOIN bookings b
                ON a.year = YEAR(b.checkin)
                AND a.month >= MONTH(b.checkin)
       GROUP BY a.year,
                b.month
       ORDER BY a.year,
                b.month
  • While it's significantly faster (17s) I can not get it to produce the results I want. I can't really explain what goes wrong but the value totals are several times larger than what they should be. – Daniel Jan 5 '15 at 18:27
  • Are there unique entries in the month_dimension table for each relevant month at this point? The only thing I can think of that would cause the issue is duplicate month entries. – Thomas Cleberg Jan 5 '15 at 18:39
  • @Daniel is correct, the query will not produce correct results in the SUM(total). Thomas, the table booking has of course multiple rows on the same year and month. – ypercubeᵀᴹ Jun 20 '16 at 8:33
0

You can use join on the same table. Its called self join.

Like:

FROM `bookings` b 
INNER JOIN bookings book2 ON b.columnname = book2.columnname
  • 1
    Interesting, will this help me in my case though? – Daniel Jan 5 '15 at 17:10

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.