I have been increasingly using immutable data storing for my web-app, due to some advantages it has (simpler writes, ability to view history / activity).
I am doing it using MySQL dabatases, simply by only doing inserts; no updates/deletes; and with the id (primary key) on each row being an autoincrementing kind of 'transaction id', and then with an 'entity id' column with an appropriate name.
The problem I'm having is massively verbose MySQL queries for certain desired latest data. With your standard mutable MySQL database, you don't have to worry about getting the latest row for each table (because it's always latest as they just use updates). With immutable data I'm having to do this huge queries full of self-joins, so it returns only the subset of data which has the highest id.
For instance:
select ...
...
inner join (
/* latest */
select y.* from (
select id,forum_id,optional, max(id) as max_id
from forum_opt
group by forum_id
) as x
inner join forum_opt as y
on y.id = x.max_id
) as forumopt on ...
Whereas before it would just be:
select ...
...
inner join forum_opt on ...
So is there a cleaner way I can implement this? Is there something I'm not understanding about this software design concept or something I'm doing wrong?
SELECT ... ORDER BY id DESC LIMIT 1
. – J_H Oct 1 '17 at 16:35