Optimizing a MySQL query summing and averaging by multiple groups over a given date range

I'm currently working on a home-grown analytics system, currently using MySQL 5.6.10 on Windows Server 2008 (moving to Linux soon, and we're not dead set on MySQL, still exploring different options, including Hadoop). We've just done a huge import, and what was a lightning-fast query for a small customer is now unbearably slow for a big one. I'm probably going to add an entirely new table to pre-calculate the results of this query, unless I can figure out how to make the query itself fast. What the query does is take @StartDate and @EndDate as parameters, and calculates, for every day of that range, the date, the number of new reviews on that date, a running total of number of reviews (including any before @StartDate), and the daily average rating (if there is no information for a given day, the average rating will be carried over from the previous day). Available filters are age, gender, product, company, and rating type. Every review has 1-N ratings, containing at the very least an "overall" rating, but possibly more per customer/product, such as "Quality", "Sound Quality", "Durability", "Value", etc... The API that calls this injects these filters based on user selection. If no rating type is specified, it uses "AND ratingTypeId = 1" in place of the AND clause comment in all three parts of the query I'll be listing below. All ratings are integers between 1 and 5, though that doesn't really matter to this query. Here are the tables I'm working with: ``` CREATE TABLE `times` ( `timeId` int(11) NOT NULL AUTO_INCREMENT, `date` date NOT NULL, `month` char(7) NOT NULL, `quarter` char(7) NOT NULL, `year` char(4) NOT NULL, PRIMARY KEY (`timeId`), UNIQUE KEY `date` (`date`) ) ENGINE=MyISAM CREATE TABLE `reviewCount` ( `companyId` int(11) NOT NULL, `productId` int(11) NOT NULL, `createdOnTimeId` int(11) NOT NULL, `ageId` int(11) NOT NULL, `genderId` int(11) NOT NULL, `totalReviews` int(10) unsigned NOT NULL DEFAULT '0', PRIMARY KEY (`companyId`,`productId`,`createdOnTimeId`,`ageId`,`genderId`), KEY `companyId_fk` (`companyId`), KEY `productId_fk` (`productId`), KEY `createdOnTimeId` (`createdOnTimeId`), KEY `ageId_fk` (`ageId`), KEY `genderId_fk` (`genderId`) ) ENGINE=MyISAM CREATE TABLE `ratingCount` ( `companyId` int(11) NOT NULL, `productId` int(11) NOT NULL, `createdOnTimeId` int(11) NOT NULL, `ageId` int(11) NOT NULL, `genderId` int(11) NOT NULL, `ratingTypeId` int(11) NOT NULL, `negativeRatings` int(10) unsigned NOT NULL DEFAULT '0', `positiveRatings` int(10) unsigned NOT NULL DEFAULT '0', `neutralRatings` int(10) unsigned NOT NULL DEFAULT '0', `totalRatings` int(10) unsigned NOT NULL DEFAULT '0', `ratingsSum` double unsigned DEFAULT '0', `totalRecommendations` int(10) unsigned NOT NULL DEFAULT '0', PRIMARY KEY (`companyId`,`productId`,`createdOnTimeId`,`ageId`,`genderId`,`ratingTypeId`), KEY `companyId_fk` (`companyId`), KEY `productId_fk` (`productId`), KEY `createdOnTimeId` (`createdOnTimeId`), KEY `ageId_fk` (`ageId`), KEY `genderId_fk` (`genderId`), KEY `ratingTypeId_fk` (`ratingTypeId`) ) ENGINE=MyISAM ``` The 'times' table is pre-filled with every day from 1900-01-01 to 2049-12-31, and the two count tables are populated by an ETL script with a roll-up query grouped by company, product, age, gender, ratingType, etc... What I'm expecting back from the query is something like this: ``` Date NewReviews CumulativeReviewsCount DailyRatingAverage 2013-01-24 7020 10586 4.017514595496247 2013-01-25 5505 16091 4.058400718778077 2013-01-27 2043 18134 3.992957746478873 2013-01-28 3280 21414 3.983625730994152 2013-01-29 4648 26062 3.921597633136095 ... 2013-03-09 1608 60297 3.9409722222222223 2013-03-10 470 60767 3.7743682310469313 2013-03-11 1028 61795 4.036697247706422 2013-03-13 494 62289 3.857388316151203 2013-03-14 449 62738 3.8282208588957056 ``` I'm pretty sure I could pre-calculate everything grouped by age, gender, etc..., except for the average, but I may be wrong on that. If I had three reviews for two products on one day, with all other groups different, and one had a rating of 2 and 5, and the other a 4, the first would have a daily average of 3.5, and the second 4. Averaging those averages would give me 3.75, when I'd expect to get 3.66667. Maybe I could do something like multiplying the average for that grouping by the number of reviews to get the total rating sum for the day, sum those up, then divide them by total ratings count at the end. Seems like a lot of extra work, but it may be faster than what I'm currently doing. Speaking of which, here's my current query: ``` SET @cumulativeCount := (SELECT coalesce(sum(rc.totalReviews), 0) FROM reviewCount rc INNER JOIN times dt ON rc.createdOnTimeId = dt.timeId WHERE dt.date < @StartDate -- AND clause for filtering by ratingType (default 1), age, gender, product, and company is injected here in C# ); SET @dailyAverageWithCarry := (SELECT SUM(rc.ratingsSum) / SUM(rc.totalRatings) FROM ratingCount rc INNER JOIN times dt ON rc.createdOnTimeId = dt.timeId WHERE dt.date < @StartDate AND rc.totalRatings > 0 -- AND clause for filtering by ratingType (default 1), age, gender, product, and company is injected here in C# GROUP BY dt.timeId ORDER BY dt.date DESC LIMIT 1 ); SELECT subquery.d AS `Date`, subquery.newReviewsCount AS `NewReviews`, (@cumulativeCount := @cumulativeCount + subquery.newReviewsCount) AS `CumulativeReviewsCount`, (@dailyAverageWithCarry := COALESCE(subquery.dailyRatingAverage, @dailyAverageWithCarry)) AS `DailyRatingAverage` FROM ( SELECT dt.date AS d, COALESCE(SUM(rc.totalReviews), 0) AS newReviewsCount, SUM(rac.ratingsSum) / SUM(rac.totalRatings) AS dailyRatingAverage FROM times dt LEFT JOIN reviewCount rc ON dt.timeId = rc.createdOnTimeId LEFT JOIN ratingCount rac ON dt.timeId = rac.createdOnTimeId WHERE dt.date BETWEEN @StartDate AND @EndDate -- AND clause for filtering by ratingType (default 1), age, gender, product, and company is injected here in C# GROUP BY dt.timeId ORDER BY dt.timeId ) AS subquery; ``` The query currently takes ~2 minutes to run, with the following row counts: ``` times 54787 reviewCount 276389 ratingCount 473683 age 122 gender 3 ratingType 28 product 70070 ``` Any help would be greatly appreciated. I'd either like to make this query much faster, or if it would be faster to do so, to pre-calculate the values grouped by date, age, gender, product, company, and ratingType, then do a quick roll-up query on that table. UPDATE #1: I tried Meherzad's suggestions of adding indexes to times and ratingCount with: ``` ALTER TABLE times ADD KEY `timeId_date_key` (`timeId`, `date`); ALTER TABLE ratingCount ADD KEY `createdOnTimeId_totalRatings_key` (`createdOnTimeId`, `totalRatings`); ``` Then ran my initial query again, and it was about 1s faster (~89s), but still too slow. I tried Meherzad's suggested query, and had to kill it after a few minutes. As requested, here is the EXPLAIN results from my query: ``` id|select_type|table|type|possible_keys|key|key_len|ref|rows|Extra 1|PRIMARY|<derived2>|ALL|NULL|NULL|NULL|NULL|6808032|NULL 2|DERIVED|dt|range|PRIMARY,timeId_date_key,date|date|3|NULL|88|Using index condition; Using temporary; Using filesort 2|DERIVED|rc|ref|PRIMARY,companyId_fk,createdOnTimeId|createdOnTimeId|4|dt.timeId|126|Using where 2|DERIVED|rac|ref|createdOnTimeId,createdOnTimeId_total_ratings_key|createdOnTimeId|4|dt.timeId|614|NULL ``` I checked the cache read miss rate as mentioned in the article on buffer sizes, and it was ``` Key_reads 58303 Key_read_requests 147411279 For a miss rate of 3.9551247635535405672723319902814e-4 ``` UPDATE #2: Solved! The indices definitely helped, so I'll give credit for the answer to Meherzad. What actually made the most difference was realizing that calculating the rolling average and daily/cumulative review counts in the same query was joining those two huge tables together. I saw that the variable initialization was done in two separate queries, and decided to try separating the two big queries into subqueries and then joining them based on the timeId. Now it runs in 0.358s with the following query: ``` SET @StartDate = '2013-01-24'; SET @EndDate = '2013-04-24'; SELECT @StartDateId:=MIN(timeId), @EndDateId:=MAX(timeId) FROM times WHERE date IN (@StartDate , @EndDate); SELECT @CumulativeCount:=COALESCE(SUM(totalReviews), 0) FROM reviewCount WHERE createdOnTimeId < @StartDateId -- Add Filters ; SELECT @DailyAverage:=COALESCE(SUM(ratingsSum) / SUM(totalRatings), 0) FROM ratingCount WHERE createdOnTimeId < @StartDateId AND totalRatings > 0 -- Add Filters GROUP BY createdOnTimeId ORDER BY createdOnTimeId DESC LIMIT 1; SELECT t.date AS `Date`, COALESCE(q1.newReviewsCount, 0) AS `NewReviews`, (@CumulativeCount:=@CumulativeCount + COALESCE(q1.newReviewsCount, 0)) AS `CumulativeReviewsCount`, (@DailyAverage:=COALESCE(q2.dailyRatingAverage, COALESCE(@DailyAverage, 0))) AS `DailyRatingAverage` FROM times t LEFT JOIN (SELECT rc.createdOnTimeId AS createdOnTimeId, COALESCE(SUM(rc.totalReviews), 0) AS newReviewsCount FROM reviewCount rc WHERE rc.createdOnTimeId BETWEEN @StartDateId AND @EndDateId -- Add Filters GROUP BY rc.createdOnTimeId) AS q1 ON t.timeId = q1.createdOnTimeId LEFT JOIN (SELECT rc.createdOnTimeId AS createdOnTimeId, SUM(rc.ratingsSum) / SUM(rc.totalRatings) AS dailyRatingAverage FROM ratingCount rc WHERE rc.createdOnTimeId BETWEEN @StartDateId AND @EndDateId -- Add Filters GROUP BY rc.createdOnTimeId) AS q2 ON t.timeId = q2.createdOnTimeId WHERE t.timeId BETWEEN @StartDateId AND @EndDateId; ``` I had assumed that two subqueries would be incredibly slow, but they were insanely fast because they weren't joining completely unrelated rows. It also pointed out the fact that my earlier results were way off. For example, from above: ``` Date NewReviews CumulativeReviewsCount DailyRatingAverage 2013-01-24 7020 10586 4.017514595496247 ``` Should have been, and now is: ``` Date NewReviews CumulativeReviewsCount DailyRatingAverage 2013-01-24 599 407327 4.017514595496247 ``` The average was correct, but the join was screwing up the number of both new and cumulative reviews, which I verified with a single query. I also got rid of the joins to the times table, instead determining the start and end date IDs in a quick initialization query, then just rejoined to the times table at the end. Now the results are: ``` Date NewReviews CumulativeReviewsCount DailyRatingAverage 2013-01-24 599 407327 4.017514595496247 2013-01-25 551 407878 4.058400718778077 2013-01-26 455 408333 3.838926174496644 2013-01-27 433 408766 3.992957746478873 2013-01-28 425 409191 3.983625730994152 ... 2013-04-13 170 426066 3.874239350912779 2013-04-14 182 426248 3.585714285714286 2013-04-15 171 426419 3.6202531645569622 2013-04-16 0 426419 3.6202531645569622 2013-04-17 0 426419 3.6202531645569622 2013-04-18 0 426419 3.6202531645569622 2013-04-19 0 426419 3.6202531645569622 2013-04-20 0 426419 3.6202531645569622 2013-04-21 0 426419 3.6202531645569622 2013-04-22 0 426419 3.6202531645569622 2013-04-23 0 426419 3.6202531645569622 2013-04-24 0 426419 3.6202531645569622 ``` The last few averages properly carry the earlier ones, too, since we haven't imported from that customer's data feed in about 10 days. Thanks for the help!