[erlang-questions] Using QLC to select unique rows

[Ben Hood]

Hi,

I have a question about to most efficiency query a large table for  
unique rows.

For example, if I have a table with 3 attributes and I want to  
retrieve all unique combinations of 2 of the attributes, the cost of  
this appears to be linear in the amount of rows (see code below).

In this example I am inserting N identical rows and 1 different row  
and then querying for the 1 row that is different from the rest.

As an alternative, I had considered decomposing the table into a  
separate table that would only store unique combinations of the  
attributes I want to query and then perform a join on the 2 tables.

However, doing so changes my entity model and would introduce more  
CRUD maintenance into the app.

Are there any good practices for doing this with QLC/mnesia or does my  
code have a glaring mistake in it?

Thx,

Ben


-module(foo).

-include_lib("stdlib/include/qlc.hrl").

-compile(export_all).

-record(a, {id,first,second}).

insert(N) ->
     mnesia:create_schema([node()]),
     mnesia:start(),
     mnesia:delete_table(a),
     mnesia:create_table(a, [{attributes, record_info(fields, a)}]),
     mnesia:add_table_index(a,first),
     mnesia:add_table_index(a,second),
     F = fun() -> write(#a{first = N,second = N},N) end,
     mnesia:ets(F).

q() ->
     Q = qlc:q([A || A <- mnesia:table(a),
                     A#a.first == "something",
                     A#a.second == "else"]),
     F = fun() -> qlc:e(Q,[unique_all]) end,
     timer:tc(mnesia,transaction,[F]).

write(_,0) ->
     mnesia:write(#a{id = 0, first = "something",second = "else"});
write(X,N) ->
     mnesia:write(X#a{id = N}),
     write(X,N-1).