The optimal index for that query is
INDEX(goal_id, year, target)
in that order. And regardless of the cardinality of any of the columns.
- Start with the
- Add on at most one range (the two
year tests are effectively one "range")
- Add on all the other columns found anywhere in the query; this makes the index "covering" for another boost. Now the entire query is performed in the index's BTree, without also having to bounce over to the data's BTree.
(That is a simplification. For more details and varied cases: http://mysql.rjweb.org/doc.php/index_cookbook_mysql )
But... That query looks like it might return lots and lots of rows. If so, you may be network-bound. If so, then think about whether you can do more processing in SQL without shoveling a million rows to the client.