All we need is an easy explanation of the problem, so here it is.
In my database, I have a prices table. Prices are valid for a set period of time or forever, but there always is a valid price at a given time.
I was wondering would you rather have a
ends_at columns, where
ends_at would always be equal to the previous
starts_at value, or only a
starts_at and query for the last one at a given date?
I can see the benefit of having
between capabilities, but on the other side it’s a hassle to update a row, since it might impact
ends_at dates here and there.
How to solve :
I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.
If you have only thousands of rows, do whatever. If you have millions of rows, then you will find that "between start and end" is inefficient, and there is no
INDEX possible to make it fast.
When you have both a start and end in each row, the Optimizer does not know whether there are overlapping ranges. So it has to assume there could be multiple results for any lookup you do.
ip-ranges shows how to make non-overlapping ranges work efficiently. (Alas, it will take some adapting to change to your application.)
You can do it either way.
If you like the idea of
between, then I would add a trigger on after insert that updates the
ends_at of the previous record/instance.
Triggers are the best way to have duplicate(non-normalised) data synchronised.
Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂