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

Questions / Help
,
#1

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 :ets.select is slow
(https://gist.github.com/maierru/1924545dfa77059262405e0f169a59e3)

#2

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.

#3

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

#4

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…

2 Likes
#5

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

2 Likes