如果您应用这两个索引，我希望您现有的查询会加快。。。

CREATE INDEX MY_TABLE_league_home_date_cust
    ON MY_TABLE(
        League, HomeTeam, MatchDate, CustomerID
    );

CREATE INDEX MY_TABLE_league_away_date_cust
    ON MY_TABLE(
        League, AwayTeam, MatchDate, CustomerID
    );

也就是说，我怀疑在您的查询中涉及的最高成本是 COUNT(DISTINCT CustomerID) . 这样做需要对所有数据进行排序。这可能意味着以下指标可能更好。。。

CREATE INDEX MY_TABLE_cust_league_home_date
    ON MY_TABLE(
        CustomerID, League, HomeTeam, MatchDate
    );

CREATE INDEX MY_TABLE_cust_league_away_date
    ON MY_TABLE(
        CustomerID, League, AwayTeam, MatchDate
    );

不过，另一个观察是，在你的提问中 COUNT() 独特的“家庭客户”和 COUNT() 独特的“客源”，然后 SUM() 他们。也就是说，任何参加过主场比赛和客场比赛的人都算两次。是有意的吗？
如果不是这样，您可能会发现查询的成本更高。。。

SELECT
  Team,
  League,
  DATE_FORMAT(MatchDate, '%Y')   AS Season,
  COUNT(DISTINCT CustomerID)     AS total
FROM 
(
  SELECT CustomerID, League, HomeTeam AS Team, MatchDate FROM MyTable
  UNION ALL
  SELECT CustomerID, League, AwayTeam AS Team, MatchDate FROM MyTable
)
  combined_view
GROUP BY
  Team, League, Season
ORDER BY
  total DESC

不过，我认为最好的办法是为 Season 然后使用稍微修改过的第一个索引。。。

ALTER TABLE
  MY_TABLE
ADD Season VARCHAR(4) AS (
  DATE_FORMAT(MatchDate, '%Y')
);

CREATE INDEX MY_TABLE_league_home_season_cust
    ON MY_TABLE(
        League, HomeTeam, Season, CustomerID
    );

CREATE INDEX MY_TABLE_league_away_season_cust
    ON MY_TABLE(
        League, AwayTeam, Season, CustomerID
    );

SELECT
  Team,
  League,
  Season,
  COUNT(DISTINCT CustomerID)     AS total
FROM 
(
  SELECT CustomerID, League, HomeTeam AS Team, Season FROM MyTable
  UNION ALL
  SELECT CustomerID, League, AwayTeam AS Team, Season FROM MyTable
)
  combined_view
GROUP BY
  Team, League, Season
ORDER BY
  total DESC

你能试试这个吗？

SELECT DATE_FORMAT(A.MatchDate, '%Y') as 'Season',  
       case c.col
         when 'home' then A.HomeTeam
         when 'away' then A.AwayTeam
       end as Teams,
       A.League, count(distinct A.CustomerID) as "totalnum" 
        FROM MY_TABLE A
        cross join ( select 'home' as col union all select 'away') c
     GROUP BY League, Teams, Season
ORDER BY totalnum DESC

在sql fiddle:new中查看结果：http://sqlfiddle.com/#!9/dd0335/11（之前：http://sqlfiddle.com/#!9/dd0335/9号文件）