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? - #12’
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
We build reliable cloud platforms for business-critical systems.
Categories:
Sub Categories:
Forums
Popular Tags
- #ecto
- #liveview
- #troubleshooting
- #learning-elixir
- #deployment
- #library
- #erlang
- #testing
- #genserver
- #mix
- #absinthe
- #remote-other
- #otp
- #plug
- #how-to-question
- #macros
- #postgres
- #channels
- #elixirconf
- #exunit
- #discussion
- #code-sync
- #javascript
- #podcasts
- #onsite
- #dialyzer
- #docker
- #authentication
- #umbrella
- #full-time-contract
- #podcasts-by-brainlid
- #ecto-query
- #elixir-ls
- #phoenix_html
- #iex
- #blog-post
- #graphql
- #genstage
- #ai
- #websockets
- #supervisor
- #advent-of-code
- #elixirconf-us
- #distillery
- #processes
- #forms
- #api
- #metaprogramming
- #security
- #performance
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.
Real-time error tracking, performance insights, and observability for devs.
Develop your skills with books, videos, and courses.
Producing high quality Elixir screencasts since 2017.
The deployment platform built for Elixir: PaaS ease, VPS control.
Courses that'll move you from confusion to "Aha, now I get it!"