[erlang-questions] Mnesia multi-table joins
Tue Sep 30 18:36:38 CEST 2008
On Tue, Sep 30, 2008 at 4:49 PM, <> 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,
> -----Original Message-----
> From: RUBINO, Dana, GBM
> Sent: 30 September 2008 15:59
> 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?
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, ...
More information about the erlang-questions