MySQL not using the correct index I have following readings table, it has around 500 million rows.

CREATE TABLE readings (
   id int(11) NOT NULL AUTO_INCREMENT,
   value double NOT NULL,
   created_dt datetime NOT NULL,
   device_id int(11) NOT NULL,
   PRIMARY KEY (id),
   UNIQUE KEY readings_created_dt_483d31a3654ede43_uniq (created_dt,device_id),
   KEY readings_device_id_6a03c4ab761154d1_fk_device_id (device_id),
   CONSTRAINT readings_device_id_6a03c4ab761154d1_fk_device_id FOREIGN KEY (device_id) REFERENCES device (id)
) ENGINE=InnoDB AUTO_INCREMENT=138611438 DEFAULT CHARSET=latin1


It has an index on (created_dt, device_id) and an index on device_id( which is foreign key)

When i run following query

SELECT value, created_dt 
FROM readings 
WHERE (created_dt BETWEEN '2019-03-17 19:11:00' AND '2019-03-18 19:11:00' 
  AND (device_id) IN (10, 11, 12));


Above query takes around 2 minutes.

DESCRIBE query returns the index used is readings_device_id_6a03c4ab761154d1_fk_device_id which is foreign key index on device_id. However if i remove the above index and run the query again, it uses readings_created_dt_483d31a3654ede43_uniq which is an index on both (created_dt, device_id) and retuns data in less than a second.
In my experience 5.6/5.7 is notorious for not spotting the correct index to choose for (range, range) type queries on large tables. You could try an analyze readings to see if it improves matters but I doubt it.

I have seen many examples of a SELECT on a large table filtered with a range drawn from a small list of possible values (your IN (1, 2, 3)) and an additional filter from small range drawn a massive spread of values (your created_dt BETWEEN) ... and at some threshold the planner will always choose to filter on the small list, leading to the results you observe.

The only way I have ever been able to solve this problem is either force the index if I can alter the query, or archive a significant (think 30-40%) chunk of the historical data to have the planner once again begin to choose the correct index.

Mysql 8 might be better, but I haven't any experience with really large tables on it yet.