[erlang-questions] Mnesia multi-table joins

[Robert Raschke]

On Tue, Sep 30, 2008 at 4:49 PM,  <Dana.RUBINO@REDACTED> wrote:
> Hi all,
>
> Changed subject of my last thread as it wasn't a performance issue!
>
> I have done some more digging and found that Mnesia appears to support only joins across two tables.
>
> I have tried a cut down version of the below query using the two big tables only and it is lightning quick.
>
> Does anyone know if there is a way to perform joins across more than two tables?
>
> I fear I will have tripped on the last hurdle (I am currently prototyping a proposed system) if I cant perform joins across more than two tables.
>
> Many Thanks,
> Dan
>
> -----Original Message-----
> From: RUBINO, Dana, GBM
> Sent: 30 September 2008 15:59
> To: erlang-questions@REDACTED
> Subject: Extremely poor Mnesia performance
>
>
> Hey all,
>
> I am doing some benchmarking with Mnesia at the moment and am pretty disappointed to say the least.
>
> I'm hoping I am doing something wrong.
>
> Running a 5 table join below: (two of the tables have ~50k rows the rest a couple of hundred)
>
> execute(qlc:q([I#i.user_id       ||   I <- mnesia:table(i),
>                                                C <- mnesia:table(c),
>                                                I#i.user_id =:= C#c.user_id,
>                                                I#i.group =:= "Group A",
>                                                P <- mnesia:table(p),
>                                                T <- mnesia:table(t),
>                                                S <- mnesia:table(s),
>                                                C#c.platform_id =:= P#p.platform_id,
>                                                C#c.trans_id =:= T#t.trans_id,
>                                                C#c.sales_id =:= S#s.sales_id
>                                                ])).
>
> On a normal RDBMS this query returns in < 1min. I am yet to get a result from Mnesia and its been running for 10 mins.
>
> Is there anyway I can see what's going wrong with this query under the hood?
>
> Thanks,
> Dan
>

Do you really need one result set that makes use of values in all five
tables? Is it possible to have a i.user_id that does not have a
corresponding c.user_id, and can the c.platform_id, ... point to
non-existant p, ... entries? If not, that is, you have (conceptual)
foreign key constraints, then you can probably cut down the query to
just the i and c tables with conditions that the c.platform_id, ...
have values.

Robby