MySQL table queries extremely slow
It sounds you have a slow disk, small ram and the main cache is too small.
How much RAM?  What is the value of innodb_buffer_pool_size.  SSD or HDD?
Without the secondary indexes, it had no choice but to read the entire table which was probably about 200MB?  But the buffer pool is only 16MB or 128MB (old defaults), hence &quot;caching&quot; was useless.
Moving to a much smaller secondary index (with 8-byte DATETIME(6) and 4-byte INT) was somewhat faster, but still too big to be cached.
I don't want to advise on the buffer_pool size without first knowing how much (or little) RAM you have and whether there are other processes running.