Simple SQL query but huge table - how to optimize?
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 = test(s)
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.