What is the correct index on a large dataset with group by?

Question

I've been managing this very large data set for mlb data in mysql for awhile and am trying to rework the indexes to increase efficiency.

I have several instances I need to manage where I need to sort by several ranges and also return results grouped by the player.

As an example:

SELECT  `pitcher`
    FROM  `pitch_stats`
    WHERE  `game_year`='2022'
      and  `release_speed` < '82'
    group by  `pitcher`;

Obviously the index should include game year first, but after that is it more efficient to index by release speed (the next search variable) or by pitcher? Thus requiring a full search but no sorting once the results are flagged.

Is there another option that I'm missing which would be more efficient? Some of these queries can take 10+ seconds which seems excessive.

Answer 1

What you need are two things

First, create an index with the three columns in this order

ALTER TABLE pitch_stats
ADD INDEX gy_pit_rs_index (game_year,pitcher,release_speed);

Second, rewrite the query to see every release_speed for the pitcher in that year and the average

SELECT pitcher
    ,GROUP_CONCAT(release_speed) all_release_speeds
    ,COUNT(release_speed) count_release_speeds
    ,AVG(release_speed) average_release_speed
FROM `pitch_stats`
WHERE `game_year`='2022'
and `release_speed` < '82'
group by `pitcher`; 

Answer 2

For that query...

1. = parts of WHERE -- game_year
2. one "range" part of WHERE -- release speed. Now (game_year, release_speed, ...)` fully covers the filtering.
3. [optional] add all other columns being used anywhere in the query -- This makes the inde "covering". EXPLAIN will say `"Using index" to indicate this.

So:

INDEX(game_year, release_speed, pitcher)

More: Index Cookbook

For Rolando's revised query, his different index may be better.