Simple SQL query but huge table - how to optimize? I've got this very simple MySQL query:

SELECT target FROM table WHERE goal_id=1 AND year&gt;=2015 AND year&lt;=2020


The table has about 5 millions rows, though. And as a result, it's very slow (about 10 seconds).

What could I do to improve things? Would an index help, and if so on which column?
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.