I've got this very simple MySQL query:

SELECT target FROM table WHERE goal_id=1 AND year>=2015 AND year<=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?

  • Show the create table to see the indexes already have and then also a EXPLAIN of your query. But simply said a combined INDEX on goal_id and year shoild move things along.
    – nbk
    Apr 16 '20 at 17:11
  • ...depending on how many distinct goal_id values there are.
    – mustaccio
    Apr 16 '20 at 17:35

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.

  1. Start with the = test(s)
  2. Add on at most one range (the two year tests are effectively one "range")
  3. 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.


I've done the following and it seems to work well so far. Thanks for all the tips!

ALTER TABLE table ADD INDEX index_name (year, goal_id)
  • 2
    Try to swap fields in the index expression - especially if goal_id have more than 7 distinct values.
    – Akina
    Apr 16 '20 at 18:31

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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