Unused index in mnesia table

[Goran Bage]

Dan Gudmundsson wrote:
> How unique was the index keys?

They were all the same as you noticed, I was aware of the fact that all were the 
same and undefined. But I did not know this was a problem and maybe there are
others that may get the same problem, or am I the only one that plan ahead :-)
> 
> If all index keys are the same you will get a quadratic insertion time,
> i.e. a long linked list with a reference to the real keys.

Quadratic was my guess looking at the figures.

> 
> That is usually the problem when indecies are slow, in your example 
> below all records will have undefined on the index position.
> 
> I.e. index 'undefined' points to a list with all keys.

That I didn't know.

> 
> /Dan
> 
> Goran Bage writes:
>  > Hello,
>  > 
>  > We found a problem with mnesia tables with an index that is unused.
>  > Loading a moderately sized table (40.000 entries) took forever
>  > (5-10 min). It turns out that the loading time grows fast with the
>  > table size (quadratic?) if an index is unused. To check it out I
>  > wrote the attached module and tested loading tables of different
>  > sizes, this is what I got just running each case once.
>  >   N      Time
>  > 100     6
>  > 1000    62
>  > 10000   9878
>  > 20000   49939
>  > 50000   328575
>  > 
>  > Removing the unused index solved the problem. The reason I added an
>  > unused index was that we will use it later on, so that is what you
>  > get for trying to think ahead :-)
>  > 
>  > -- 
>  > -- Goran
>  > ------------------------- May the Snow be with you ----
>  >   Goran Bage,          MobileArts,     www.mobilearts.se
>  >   Tjärhovsgatan 56,    SE-116 28 Stockholm,       Sweden
>  >   email:goran.bage@REDACTED,  phone: +46 733 358405
>  > 
>  > 
>  > %%%-------------------------------------------------------------------
>  > %%% File    : mtest.erl
>  > %%% Author  : Goran Bage <goran.bage@REDACTED>
>  > %%% Description : 
>  > %%%
>  > %%% Created : 18 Nov 2004 by Goran Bage <goran.bage@REDACTED>
>  > %%%-------------------------------------------------------------------
>  > -module(mtest).
>  > 
>  > -export([create/0,
>  > 	 fill/1,
>  > 	 test/0
>  >         ]).
>  > 
>  > %%--------------------------------------------------------------------
>  > %% Records
>  > %%--------------------------------------------------------------------
>  > -record(item, {key,
>  > 	       index,
>  > 	       data}).
>  > 
>  > %%====================================================================
>  > %% External functions
>  > %%====================================================================
>  > create() ->
>  >     mnesia:create_schema([node()]),
>  >     mnesia:start(),
>  >     {atomic, ok} = 
>  > 	mnesia:create_table(item,
>  > 			    [{attributes, record_info(fields, item)},
>  > 			     {index, [index]},
>  > 			     {type, set},
>  > 			     {disc_copies, [node()]}]).
>  > 
>  > test() ->
>  >     mnesia:stop(),
>  >     mnesia:start(),
>  >     {Time, V} = timer:tc(mnesia, wait_for_tables, [[item], 10*60*1000]),
>  >     N = length(mnesia:dirty_all_keys(item)),
>  >     io:format("Size = ~w, Time = ~w, Res = ~p\n", [N, Time, V]).
>  > 
>  > 
>  > fill(N) ->
>  >     I = length(mnesia:dirty_all_keys(item)),
>  >     fill(I, N),
>  >     ok.
>  >     
>  > 
>  > %%====================================================================
>  > %% Internal functions
>  > %%====================================================================
>  > 
>  > fill(I, N) when I < N ->
>  >     mnesia:dirty_write(#item{key = I, data = any}),
>  >     fill(I+1, N);
>  > fill(_, _) ->
>  >     ok.
> 

-- 
-- Goran
------------------------- May the Snow be with you ----
  Goran Bage,          MobileArts,     www.mobilearts.se
  Tjärhovsgatan 56,    SE-116 28 Stockholm,       Sweden
  email:goran.bage@REDACTED,  phone: +46 733 358405