I am not a MySQL expert (or even a user!) but the ideal index for your query appears to be as shown below:
create table `single` (
`game_id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`user_id` mediumint(9) NOT NULL,
`stage` tinyint(4) NOT NULL,
`highscore` bigint(20) unsigned NOT NULL,
`played_date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`game_id`),
KEY `user_id_stage` (`stage`,`user_id`, `highscore`)
)
The plan I get is:
See the SQLfiddle
Much more efficient to do this without having to go back and join to the periods table.
DECLARE @StartDate DATE, @EndDate DATE;
Select @StartDate = Min(StartDate), @EndDate = MAX(EndDate)
from dbo.PeriodCalendar_Weeks pcw
where (pcw.Year = @Year and pcw.Period < @Period)
or (pcw.Year = @Year and pcw.Period = @Period and pcw.Week <= @Week)
or (pcw.Year = @Year -1 and pcw.Period >= @Period);
SELECT
WeekEndDate = DATEADD(DAY, 6, DATEADD(WEEK, SalesWeek, @StartDate)),
Store,
DeliveryChargesTotal = dct
FROM
(
SELECT DATEDIFF(DAY, @StartDate, SalesDate)/7, Store, SUM(DeliveryChargesTotal)
FROM dbo.Daily_GC_Headers
WHERE SalesDate BETWEEN @StartDate AND @EndDate AND isCanceled = 0
GROUP BY DATEDIFF(DAY, @StartDate, SalesDate)/7, Store
) AS x (SalesWeek, Store, dct)
ORDER BY WeekEndDate, Store;
A filtered index may help, if many rows exist where isCanceled = 1 (these are just possible suggestions, depending on cardinality of Store, and may not be the most optimal):
CREATE INDEX x ON dbo.Daily_GC_Headers
(SalesDate) INCLUDE (Store, DeliveryChargesTotal)
WHERE isCanceled = 0;
If there are very few rows where isCanceled = 1, this may be better:
CREATE INDEX x ON dbo.Daily_GC_Headers
(SalesDate, IsCanceled) INCLUDE (Store, DeliveryChargesTotal);
Both are worth trying on a test system, as well as moving Store into the key in either case, or moving IsCanceled to the INCLUDE list in the latter case. On my system, I found the best results with everything but the date in the INCLUDE list:
CREATE INDEX x ON dbo.Daily_GC_Headers
(SalesDate) INCLUDE (Store, IsCanceled, DeliveryChargesTotal);
Again, you will need to test if any of these work out, or if the query above gives a different/better recommendation directly from SQL Server.
Best Answer
In the version of the query with
FORCESEEK, the majority of the query cost is in the key lookup. You can eliminate this key lookup by creating a nonclustered index onGridRunIdandMancoId.Have a look at the estimated cost figure in the
SELECT(root) node of your plans. The optimizer will chose the plan with the lowest estimated cost. By adding a covering nonclustered index, you eliminate the need for the key lookup. This should make the estimated cost of the index seek less than the index scan. You might also want to run the query withOPTION (RECOMPILE)to make sure it uses a fresh execution plan. The index could look something like this:The optimizer would ordinarily cost the scan higher than the seek, but the effect of the
TOPmeans the optimizer computes costs for the desired number of rows (a 'row goal') rather than for the whole set (it estimates that a partial scan will locate the 15 desired rows more efficiently than the seek + lookup combination).