I have a mnesia table with fields like id, name, city... I would like to select/match name using prefix, for example if there are multiple records with names starting with "joe" i would like to select all of them efficiently without iterating all the records.
Just like sql query "select id from mytbl where name like joe%". Thanks
Update:
Benchmarks based on 1000 records:
MySql(name also a primary key): 450-500 microseconds
Mnesia actual table(not key): 300-370 microseconds
Separate ordered_set mnesia table with name as key: 60-108 microseconds
Note: mnesia tables are ram only, i didn't notice any performance difference even after indexing name field for actual table.
Something like:
mnesia:select(mytbl, ets:fun2ms(fun(#mytbl{name = "joe" ++ _, id = Id}) -> Id end)).
or
mnesia:select(mytbl, [{#mytbl{name = "joe" ++ '_', id = '$1', _ = '_'},[],['$1']}]).
(That is a match specification. The two variants are equivalent, but it's usually clearer to use ets:fun2ms to generate match specifications. You'll need -include_lib("stdlib/include/ms_transform.hrl"). for this to be accepted by the compiler. Note that if you try this in the shell, only the latter will work, because of an inconsistency in how ++ is handled by ets:fun2ms.)
Note that behind the scenes this will still iterate through all the records, unless your table is of type ordered_set and name is the primary key.
If you want to get the full record, and not just the id field, that would be:
mnesia:select(mytbl, ets:fun2ms(fun(#mytbl{name = "joe" ++ _} = X) -> X end)).
or
mnesia:select(mytbl, [{#mytbl{name = "joe" ++ '_', _ = '_'},[],['$_']}]).