Mnesia 100x slower to query compared with SQLite?

Hi folks,

I just started to explore the Mnesia database. I read many docs, posts and resources provided by Elixir forum, but still could not found a solution:

My Mnesia is 100x times slower to query (scan) compared with Ecto/SQLite3.

Initialization

The following code shows how Mnesia was initialized:

defmodule Data.Test do
  use Memento.Table,
    attributes: [
      :uuid,
      :name
    ],
    index: [:name]
end

Memento.stop()
Memento.Schema.create([node()])
Memento.start()

Memento.Table.create!(Data.Test, disc_copies: nodes)

Memento.transaction!(fn ->
  Enum.each(1..100_000, fn i ->
    Memento.Query.write(%Data.Test{
      uuid: Ecto.UUID.generate(),
      name: "Test #{i}"
    })
  end)
end)

Then I created an exactly same table in SQLite3, without index.

Query

The following code shows the query process:

:timer.tc(fn ->
  Memento.transaction!(fn ->
    Memento.Query.all(Data.Test)
    |> Stream.filter(&String.contains?(&1.name, "12345"))
    |> Enum.to_list()
  end)
end)

Test.Repo.query("select * from test where name like '%12345%'")

The result was about 1600 ms and 16 ms respectively.

I replaced the Stream in Mnesia query with :mnesia.foldl (without Memento) and the result was almost identical (1400 ms maybe, still ~100x times).

Question

My understanding is that SQL like '%xxx%' leads to a full table scan, which is same as Mnesia.

Since Mnesia stores the data in memory, why is the table scan so slow? Am I miss anything?

Any clue is sincerely appreciated!

hi, try mnesia directly maybe get a better answer

1 Like

The queries are not comparable at all.

In SQLITe everything its done by the database engine, but in the Mnesia query you are splitting the work between Mnesia and Elixir.

You can read the Memento Docs to see how you can properly do your SQLITe query only with Memento or you can read the Mnesia docs:

2 Likes

As mentioned, I did. :slight_smile:

It saved the execution time from 1600ms to 1400ms, still not comparable to SQLite.

You are right. I understood this this part. :slight_smile:

The problem is, Memento does not support string match of %key%. I also checked the Mnesia documents and the nearest thing I could found was foldl, which was still not fast…

Is there a better way to accomplish this in Mnesia, apart from Memento.Query.all() or foldl?

Mnesia is more of a key-value storage than a full-featured database.

1 Like

mnesia and ets are key/value database. It is best used when you have a specific value to retrieve.

That said, if you can structure your object in a way that :mnesia.select can be used then it is much faster. Mnesia select relies on match specifications (Erlang -- Match Specifications in Erlang) which are a bit gnarly to get your head around but very useful, not only in mnesia but also in ets and if you are doing tracing.

Match specifications do not support matching on parts of binary strings so your specific example would not work as is, but they are a faster way to get information out of mnesia and ets compared to foldl or iterating the table.

I see that Memento also support select (a simplified version of erlang’s matchspecs) and select_raw with the full power of select. Perhaps it is worth investigating.

4 Likes

Thank you all for your replies!

I think I made a mistake. When I did the test, the schema of Mnesia contains more attributes than SQLite. It should be 10x times slower instead of 100x when iterate Mnesia, while accessing via key is instant.

Having said that, is it better to use SQL when storing data that will probably be queried not only by keys?

What would you use Mnesia for? Storing runtime configurations? Caches?