Qqwy
How to efficiently order and filter query results using Mnesia?
This topic is somewhat related to the topic ‘Mnesia vs Cassandra (vs CouchDB vs ...) - your thoughts?’
I am currently running/building a chat application using Mnesia (to be more specific, I am using the EctoMnesia Ecto Adapter, but am rapidly approaching its limits, because it contains a couple of unfinished, unstable and non-existent functionalities)
When a user sees the chat screen for the first time, I want to show the 20 newest messages. And when the user scrolls backwards through history, I want to fetch the 10 messages before the oldest message’s datetime.
In other words: I want to select all messages older than a given datetime, order the results by inserted_at (in descending order), and then take only the first ten from them.
AFAIK, EctoMnesia will sort/limit in Elixir-land on the lists that are the results of the :mnesia-call, meaning that all calls will become slower (with O(n log n) time complexity) as more messages are part of the table.
I’d like to know:
- Is there a way for Mnesia to perform such a query faster?
- I know there is QLC, is it able to do so (faster than the ‘Elixir running on the output’ implementation) by using cursors or the like, or not?
- Or is Mnesia not at all able to do this, unless I alter my primary keys to be e.g. Snowflakes (IDs that are a combination of the current datetimestamp and a random number)?
Most Liked
tty
QLC provides cursors for Mnesia.
-
Since you want newest messages, any increasing integer count is sufficient for the primary key. Grab just the keys, sort and split then read only the recent 20.
-
You could use a 2-stage table, put messages into a “recent table” and move older ones into a “long-term storage table”
jordiee
You could have another value in your stored object that is an integer based on previous inserts. Then in order to travel the documents 20 at a time you could take the last record from the previous search and use it as the news value to search greater then in a select search.
Popular in Questions
Other popular topics
Latest on Elixir Forum
Sponsor Spotlight
Develop your skills with books, videos, and courses.
Categories:
Sub Categories:
Forums
Our Sponsors
Build Elixir applications with speed and confidence.
Supporting innovation across the BEAM ecosystem.
We build reliable cloud platforms for business-critical systems.
Catch errors, track performance, monitor hosts and more.
Develop your skills with books, videos, and courses.
Producing high quality Elixir screencasts since 2017.
Enabling companies to succeed by building software people love.
Courses that'll move you from confusion to "Aha, now I get it!"