Re: Index with two date fields using comparison operators

[Rick James]

Having two indexes, one starting with valid_from, one starting with valid_till, gives the optimizer two things to try. It will pick the one that works better with the constants involved. That is, don't simply assume that starting with valid_till is 'right' based on a single test.

Also, your test case is still very small. I suggest you get at least a thousand rows, and play with a variety of constants before drawing any empirical conclusions.

When the data gets too big to be cached, but the index is still cacheable, the only trick that will help some is to first find the ids, then fetch the rest of the data. It's a prime case for "index merge", if you can get it to work.

I'll tentatively vote for (when the table is big):

INDEX(valid_till, valid_from, id) -- useful for an 'old' valid_till
INDEX(valid_from, valid_till, id) -- useful for an 'recent' valid_from
SELECT  s.*
    FROM subreg s
    JOIN  
      ( SELECT  id
            FROM  subreg
            WHERE  valid_from > '...'
              AND  valid_till < '...'  ) m_from ON s.id = m_from.id

That should
1. do the subquery "Using index"
2. only then, reach back into subreg to get "*", and only reach for the desired number of rows.