Say I have a table
( person_id INT AUTO_INCREMENT, birthday_timestamp BIGINT, country, state, ..., PRIMARY KEY (person_id), KEY (birthday_timestamp) )
where people are added the instant they're born. In other words, the order of
person_id is the same as that of
birthday_timestamp. More formally, if I had two rows,
A.person_id < B.person_id --> A.birthday_timestamp <= B.birthday_timestamp
Would it be reasonable to assume that performance on a
birthday_timestamp range should not be significantly worse than the equivalent WHERE on
My rudimentary understanding of how this would be executed is the engine would fetch the list of
person_id in the secondary index on
birthday_timestamp and then fetch those results from the clustered index. Since these
person_ids will be 100% contiguous, this should lead to minimal fetching of clustered index pages, right?
In contrast, if instead of
birthday_timestamp, I had something whose order was completely irrelevant...say,
favorite_number, this wouldn't be the case. In general, the
person_id's corresponding to favorite numbers between 15 and 1,000,000 would be completely spread out and non-contiguous.
Back to the former case. The one alternative I can think of that wouldn't involve a join at all is the following. Say I want
country, etc., for people born between 0 and 12345678. I could do
select * from people where birthday_timestamp >= 0 and birthday_timestamp <= 12345678
Which is what I described above and I assume would necessitate a join since MySQL doesn't actually know the keys are parellel, OR I could do two (almost)-point queries into the secondary index to grab the primary key range myself, i.e.
start = $(select person_id from people where birthday_timestamp >= 0 order by birthday_timestamp asc limit 1) end = $(select person_id from people where birthday_timestamp <= 12345678 order by birthday_timestamp desc limit 1) select * from people where person_id >= start and person_id <= end
Which seems like it could be a bit more optimal, though not by an order of magnitude. The downside is it's less concise.