ETS select is slow, how to find visits in unixtime range quickly?

I have ~10 millions of this data structure - {id, userid, locationid, visited_at, mark}

For example

iex(15)> :ets.lookup(:visits,4900032)
[{4900032, 475422, 182770, 1024411746, 2}]

How to quickly find visits some user between unixtime range, If I’ve founded out is slow

Ets does a sequential scan for any query that is not the primary key. If you have to query the time stamp you have to build an index on your own.

Is it possible to make index for non-unique unixtime field?

Of course it is…

In the index you then have to map the timestamp 123456789 to the list of ids [1, 5, 10]

Oh, and before I forget, you need to build and store the index on your own. Perhaps another ETS, or a GenServers state…


For note, mnesia can do all that index handling for you (while adding transaction support and more as well).