<html><head></head><body>Alexander, thanks for a great example of a double-linked list implementation in a database.<br>
<br>
As we can see one insert operation requires 3 write operations in the database in this case. Anyway this is still a constant factor applied to a normal write operation.<br>
<br>
Wear we never heard is how the messages are read and if they are removed from the database. If as in most chat applications they need to be read and deleted in bulk, this approach might be tedious as you need to read the messages one by one to find out the next id.<br>
<br>
I suggest to try using table of type bag and see if performance it provides is enough for all the task use cases. I believe that implementation would be easiest to read and maintain. <br>
<br>
Another approach might be to use ordered set with records of following structure: {{UserId, Timestamp}, Message}.<br><br><div class="gmail_quote">On 8 June 2016 5:32:14 AM AEST, Aleksander Nycz <Aleksander.Nycz@comarch.pl> wrote:<blockquote class="gmail_quote" style="margin: 0pt 0pt 0pt 0.8ex; border-left: 1px solid rgb(204, 204, 204); padding-left: 1ex;">
<pre class="k9mail">Hello,<br /><br />Try this:<br /><br />Users table:<br /><br />{userId, firstMessageId, lastMessageId}<br /><br />Messages table:<br /><br />{MessageId, prevMessageId, nextMessageId, Message}<br /><br /><br />Insert 1st message for user 1000:<br /><br />Users table:<br /><br />{1000, 1, 1}<br /><br />Message table:<br /><br />{1, undefined, undefined, "message1"}<br /><br /><br />Insert 2nd message for user 1000:<br /><br />Users table:<br /><br />{1000, 1, 2}<br /><br />Message table:<br /><br />{1, undefined, 2, "message1"}<br /><br />{2, 1, undefined, "message2"}<br /><br />And so on...<br /><br />You can also agregate messages (yearly/monthly/daily):<br /><br />{{UserId, Year}, firstMessageId, lastMessageId}<br /><br /><br />Regards<br /><br />Aleksander<br /><br /><br />W dniu 2016-06-03 o 19:08, Khitai Pang pisze:<br /><blockquote class="gmail_quote" style="margin: 0pt 0pt 1ex 0.8ex; border-left: 1px solid #729fcf; padding-left: 1ex;"> Hi,<br /><br /> I
need to store a list of unread messages for every user id. The order<br /> of the messages needs to be preserved. Currently I store the messages<br /> in a list as the value of the user id key in a table of type set:<br /><br /> {user_id, [message]}<br /><br /> The table is a disc_copies table.<br /><br /> Whenever someone sends a message to a user, the message is prepended to<br /> the head of the message list of the user.<br /><br /> Now my concern is, adding a new message to the list requires reading and<br /> writing the whole list, which can be pretty slow if the list is very<br /> long, i.e. thousands of messages.<br /><br /> There is another way to do this, which uses a table of type bag and<br /> composite keys:<br /><br /> {user_id, timestamp1, message1}<br /> {user_id, timestamp2, message2}<br /> {user_id, timestamp3, message3}<br /> ...<br /><br /> But I don't think this is any better because I heard that mnesia bag<br /> tables performance is very bad:<br /> <a
href="http://erlang.org/pipermail/erlang-questions/2011-March/057044.html">http://erlang.org/pipermail/erlang-questions/2011-March/057044.html</a><br /><br /> Are there other ways to store lists of messages in mnesia where<br /> reading/updating the message lists can be very efficient?<br /><br /><br /> -Khitai<br /><hr /><br /> erlang-questions mailing list<br /> erlang-questions@erlang.org<br /> <a href="http://erlang.org/mailman/listinfo/erlang-questions">http://erlang.org/mailman/listinfo/erlang-questions</a><br /></blockquote><br /></pre><p style="margin-top: 2.5em; margin-bottom: 1em; border-bottom: 1px solid #000"></p><pre class="k9mail"><hr /><br />erlang-questions mailing list<br />erlang-questions@erlang.org<br /><a href="http://erlang.org/mailman/listinfo/erlang-questions">http://erlang.org/mailman/listinfo/erlang-questions</a><br /></pre></blockquote></div><br>
-- <br>
Best wishes, <br>
Dmitry Belyaev</body></html>