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
, and B
,
A.person_id < B.person_id --> A.birthday_timestamp <= B.birthday_timestamp
Would it be reasonable to assume that performance on a WHERE
by birthday_timestamp
range should not be significantly worse than the equivalent WHERE on person_id
range?
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_id
s 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 person_id
, 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.
Any thoughts?
Thanks
AUTO_INCREMENT
:PRIMARY KEY(timestamp, id), INDEX(id)
.