For a hackathon a few years back I attempted to make the InnoDB engine in MySQL employ temporal queries. In theory, any change to a row would be timestamped, and `UPDATE` and `DELETE` remapped to `INSERT`.
The query plan for a regular query would be modified so that all referenced rows would be restricted to the matching row with the most recent timestamp. A temporal query would be restricted to the row with the most recent timestamp that was less than or equal to the provided time.
Comments
For a hackathon a few years back I attempted to make the InnoDB engine in MySQL employ temporal queries. In theory, any change to a row would be timestamped, and `UPDATE` and `DELETE` remapped to `INSERT`.
The query plan for a regular query would be modified so that all referenced rows would be restricted to the matching row with the most recent timestamp. A temporal query would be restricted to the row with the most recent timestamp that was less than or equal to the provided time.
The proposed solutions add complexity. Complexity make things brittle.
I'd think carefully before adding more complexity. Of course this is depending on what you are doing (accounting app vs a HN clone for example).