Re: How MySQL Uses Indexes

[Rick James]

> why does not mysql use index when necessary, should I force the use of index manually?

Generally, no.

Finding when something is between two columns is especially difficult for the optimizer. (I assume your `i` and `o` are start and end times?

In that use case, typically, each part of the WHERE clause will match maybe half of the table -- that's not very useful for filtering.

> I conclude that mysql optimizes the query for the best performance,

Yes, even if it means ignoring all the indexes. (Sometimes the optimizer will goof, but not very often.)

I suspect you have over-simplified your real case. There are other tricks that may apply; can we see the real CREATE TABLE and the real SELECT?