Query Optimizer on Fragmented Mnesia Table Bypassed on Indexed values..?

[Ram-A Kumar]

Hi,

I have a fragmented mnesia table  with 32 fragments and a total of 722367 
records distributed across the fragments.

The 3rd and 4th field of my Mnesia table are indexed.

I perform a simple qlc join as follows

QQ = qlc:q([list_to_tuple(tuple_to_list(X)++tuple_to_list(Y)) || X 
<-mnesia:table(Mnesia_Tab), Y <-  Data,    element(4, X) =:= element(1, 
Y)]),
F = fun()-> qlc:cursor(QQ) end,
mnesia:activity(async_dirty, F, [], mnesia_frag).

After handling the cursor it returns  125487  results in 6 secs. So far so 
good ! Mnesia is cool !

However, if you change the query a little bit

L1 = 4,
L2 = 1,
and change the statement to 
QQ = qlc:q([list_to_tuple(tuple_to_list(X)++tuple_to_list(Y)) || X 
<-mnesia:table(Mnesia_Tab), Y <-  Data,    element(L1, X) =:= element(L2, 
Y)])
it takes 24 secs to return the query with same 125487 entries.
Further, even this takes 24 secs
QQ = qlc:q([list_to_tuple(tuple_to_list(X)++tuple_to_list(Y)) || X 
<-mnesia:table(Mnesia_Tab), Y <-  Data,    element(4, X) =:= element(L2, 
Y)])

After looking at the qlc:info(QQ), i have noticed that the query optimizer 
gets bypassed when it takes 24 secs..
The first one gets converted into
begin
    V1 =        qlc:q([[G1|G2] ||
        G2 <-
        [{\"20\",\"NY\"},\n 
        {\"101\",\"NYC\"},\n
        {\"103\",\"NYC\"},\n
        {\"108\",\"NYC\"},\n
        {\"112\",\"NYC\"}],\n
        G1 <-\n 
        mnesia:table(user_feed_frag,\n
        [{n_objects,100},{lock,read}]),\n
        element(1, G1) =:= element(4, G2)],\n
        [{join,lookup}]),\n 
        qlc:q([list_to_tuple(tuple_to_list(X) ++ tuple_to_list(Y)) ||
        [X|Y] <- V1])\nend

The query otimizer got called ..

The latter got converted into
qlc:q([list_to_tuple(tuple_to_list(X) ++ tuple_to_list(Y)) ||
           X <-\n 
                   mnesia:table(user_feed_frag,\n 
                   [{n_objects,100},{lock,read}]),\n 
                   Y <-\n 
                   [{\"20\",\"NY\"},\n 
                   {\"101\",\"NYC\"},\n 
                   {\"103\",\"NYC\"},\n 
           {\"108\",\"NYC\"},\n 
                   {\"112\",\"NYC\"}],\n 
                   element(L1, X) =:= element(L2, Y)

The query optimizer DOES NOT get called..

I have tried passing MACROS and FUNS inside the qlc:q  but looks like the 
query optimizer does not recognize unless it gets the index values at 
compile time..



Does anybody has faced similar problems...or any suggestions..
Thanks for reading the post..

PS: If you try returning a list of 240,000 the node crashes..Thats another 
problem 



Kind regards,
Ram-A Kumar



---
This communication may contain confidential and/or privileged information.
If you are not the intended recipient (or have received this communication
in error) please notify the sender immediately and destroy this
communication. Any unauthorized copying, disclosure or distribution of the
material in this communication is strictly forbidden.

Deutsche Bank does not render legal or tax advice, and the information
contained in this communication should not be regarded as such.