0

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.

1
  • 1
    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? Why not trying and see which one is faster ? explain will show you better Sep 25, 2023 at 18:05

2 Answers 2

0

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`; 
3
  • Why the added aggregates to the SELECT clause?...perhaps something OP said in a comment no longer around?
    – J.D.
    Sep 25, 2023 at 18:36
  • @J.D. just some added info in case it is useful Sep 25, 2023 at 19:27
  • Thanks for the response @RolandoMySQLDBA For clarification, the second part of your post is just an example as to how you could do more with the query right? It is not necessary for a quicker response with the results?
    – Keith
    Sep 25, 2023 at 21:01
0

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.