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)?
Trending in Questions
Other Trending Topics
Latest on Elixir Forum
Sponsor Spotlight
Practical resources that improve the lives of professional developers.
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
- #elixirconf-us
- #websockets
- #supervisor
- #advent-of-code
- #distillery
- #processes
- #api
- #forms
- #metaprogramming
- #performance
- #security
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.
Error tracking, logs, uptime, and Just Enough APM. Built for developers.
Practical resources that improve the lives of professional developers.
Develop your skills with books, videos, and courses.
Producing high quality Elixir screencasts since 2017.
The team behind Membrane, Popcorn, LiveDebugger. Available for hire.
Courses that'll move you from confusion to "Aha, now I get it!"
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.
Last Post!
jordiee
Sure, I was talking store the Id much in the way an auto Inc field works in like postgres. And then use mnesia select to sort by only greater then the last returned Id. You can probably do the same thing with a date inserted field but I have normally done it with id’s