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!