How to speed up BETWEEN operation on several million items?

I have table (very simplified) in MySQL:

id      | name | datetime            | label
1       | a    | 2019-10-01 10:00:01 | null
2       | a1   | 2019-10-01 10:00:03 | null
3       | a2   | 2019-10-01 10:00:14 | null
....
4000000 | z1   | 2019-10-01 23:12:41 | null

Now I’m executing operation:

UPDATE my_table SET label = “xxx” WHERE datetime BETWEEN ‘2019-10-01 13:00:00’ AND ‘2019-10-01 13:30:00’

This is done correctly, but it takes a long, long time (over 30 seconds). How can I speed up my operation? Maybe something other instead of BETWEEN? Indexes? What indexes should I set up?

Answer

For this query:

UPDATE my_table
    SET label = "xxx"
    WHERE datetime BETWEEN '2019-10-01 13:00:00' AND '2019-10-01 13:30:00'

You can add an index on datetime:

CREATE INDEX idx_my_table_datetime ON my_table(datetime);

Advertisement

Answer