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.