<div dir="ltr">There is a patch included in the today's release that address this issue in a simpler way,<div style>for set (and ordered set if I remember correctly).</div><div style><br></div><div style>Can you check if the performance is good enough for you in R16B01.</div>
<div style><br></div><div style>/Dan</div></div><div class="gmail_extra"><br><br><div class="gmail_quote">On Wed, Jun 19, 2013 at 6:51 PM, Aleksander Nycz <span dir="ltr"><<a href="mailto:Aleksander.Nycz@comarch.pl" target="_blank">Aleksander.Nycz@comarch.pl</a>></span> wrote:<br>
<blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex">Hello,<br>
<br>
Mnesia gives possibility to create table indexes, when<br>
the user wants to frequently use some other field<br>
than the key field to look up records.<br>
<br>
Current index solution in mnesia uses ets table (type bag or duplicated_bag) to maintain mapping:<br>
Indexed field value -> Primary key value.<br>
<br>
Unfortunatelly current solution has very significant disadvantage:<br>
operation performance (loading table, insert new records,<br>
delete records, etc.) is very low when index is set on 'Low-cardinality column'<br>
<br>
<a href="http://en.wikipedia.org/wiki/Cardinality_%28SQL_statements%29" target="_blank">http://en.wikipedia.org/wiki/<u></u>Cardinality_%28SQL_statements%<u></u>29</a><br>
<br>
In such case operation complexity is O(n) when n is number<br>
of Primary Key Values. For small n performance can be acceptable for some application,<br>
but when n is the hundreds, thousands or even more such index<br>
are useless. New index type provides O(1) complexity.<br>
<br>
This patch introduces new index type in mnesia database.<br>
Main concept is to maintain all Primary Key Values not direcly in<br>
bag/duplicated_bag ets but in set of ets.<br>
For each Indexed field value new ets is created<br>
and Primary Key Values are strored in this ets.<br>
For 'Low-cardinality column' there is only a few Indexed key value (eg. isActive (true/false), state (new/pending/suspended/active)<u></u>, ...)<br>
so memory overhead for ets is not significant.<br>
<br>
Standard index:<br>
Indexed field value -> [Primary key value]<br>
<br>
New index based on ets:<br>
Indexed field value -> ets, that contains Primary key value<br>
<br>
Restrictions:<br>
<br>
1. New index can be created on disc_copies or ram_copies tables only. Tables disc_only_copies are not supported.<br>
2. Index type can't be changed. The only way to change existing index idx_list to idx_ets and vice versa<br>
is to delete existing index and create new one by mnesia:add_table_index/3 (new function, see below)<br>
<br>
<br>
New API:<br>
<br>
1. Define index type when table is created:<br>
<br>
create_table(Name, TabDef) -> {atomic, ok} | {aborted, Reason}<br>
<br>
New TabDef value:<br>
{index_type, [{atom() | int(), 'idx_std' | 'idx_ets'}]} - 'idx_std' is default when index is created<br>
<br>
Example:<br>
<br>
-type(poolId() :: integer()).<br>
-type(bucketId() :: integer()).<br>
-type(resourceState() :: free | reserved | gracePeriod).<br>
<br>
-record(rmResource, {id :: {poolId(), any()}<br>
,state :: {poolId(), bucketId(), resourceState()}<br>
,availableFrom :: integer()<br>
,availableTo :: integer()<br>
,requestorId :: any()<br>
,reservedFrom :: integer()<br>
,reservedTo :: integer()<br>
,isDeleted = false :: boolean()<br>
,mTime :: integer()}).<br>
<br>
{atomic,ok} = mnesia:create_table(<u></u>tRMResources<br>
,[<br>
{disc_copies, []}<br>
,{ram_copies, [node()]}<br>
,{type,set}<br>
,{attributes,record_info(<u></u>fields, rmResource)}<br>
,{record_name, rmResource}<br>
,{index, [state, requestorId, mTime]}<br>
,{index_type, [{state, idx_ets}, {requestorId, idx_std}]}<br>
]),<br>
<br>
2. Add new index to existing table:<br>
<br>
mnesia:add_table_index(Tab, AttrName, IndexOpts) -> {aborted, R} | {atomic, ok}<br>
<br>
This function creates a index on Mnesia table called Tab on AttrName<br>
field according to the argument IndexOpts.<br>
This list must be a list of {Item, Value} tuples, currently only one<br>
option is allowed:<br>
{index_type, 'idx_std' | 'idx_ets'}<br>
<br>
Example:<br>
<br>
mnesia:add_table_index(<u></u>tRMResources, isDeleted, [{index_type, 'idx_ets'}])<br>
<br>
3. New match_object/4, dirty_match_object/3 functions:<br>
<br>
match_object(Tab, Pat, Limit, LockKind) -> [Record] | transaction abort.<br>
dirty_match_object(Tab, Pat, Limit) -> [Record] | exit({aborted, Reason}).<br>
<br>
Similar to match_object/3 and dirty_match_object/2, but returns no more than Limit records.<br>
<br>
<br>
4. New index_match_object/5, dirty_index_match_object/4 functions:<br>
<br>
index_match_object(Tab, Pat, Attr, Limit, LockKind) -> [Record] | transaction abort.<br>
dirty_index_match_object(Tab, Pat, Attr, Limit) -> [Record] | exit({aborted, Reason}).<br>
<br>
Similar to index_match_object/4, dirty_index_match_object/3 but returns no more than Limit records.<br>
<br>
<br>
5. New index_read/4, dirty_index_read/4 functions:<br>
<br>
index_read(Tab, Key, Attr, Limit) -> [Record] | transaction abort.<br>
dirty_index_read(Tab, Key, Attr, Limit) -> [Record] | exit({aborted, Reason}).<br>
<br>
Similar to index_read/3, dirty_index_read/3 but returns no more than Limit records.<br>
<br>
<br>
6. New select_limit/3, select_limit/4, dirty_select/3 functions;<br>
<br>
select_limit(Tab, MatchSpec, NObjects [, Lock]) -> [Object] | transaction abort.<br>
<br>
Similar to select(Tab, MatchSpec [, Lock]) but returns maximum NObjects<br>
records, of course empty list can also be returned.<br>
Continuation (see select/4) is not possible. This function can also use<br>
indexes to find matching records<br>
as contrasted with select/4.<br>
<br>
dirty_select(Tab, Spec, Limit) -> [Object] | exit({aborted, Reason}.<br>
<br>
Similar to dirty_select/2 but returns no more than Limit records.<br>
<br>
And git links:<br>
<br>
git fetch git://<a href="http://github.com/nyczol/otp.git" target="_blank">github.com/nyczol/otp.<u></u>git</a> mnesia_new_index<br>
<br>
<a href="https://github.com/nyczol/otp/compare/erlang:master...mnesia_new_index" target="_blank">https://github.com/nyczol/otp/<u></u>compare/erlang:master...<u></u>mnesia_new_index</a><br>
<a href="https://github.com/nyczol/otp/compare/erlang:master...mnesia_new_index.patch" target="_blank">https://github.com/nyczol/otp/<u></u>compare/erlang:master...<u></u>mnesia_new_index.patch</a><br>
<br>
Regards,<br>
Aleksander Nycz<span class="HOEnZb"><font color="#888888"><br>
<br>
-- <br>
Aleksander Nycz<br>
Senior Software Engineer<br>
Telco_021 BSS R&D<br>
Comarch SA<br>
Phone: <a href="tel:%2B48%2012%20646%201216" value="+48126461216" target="_blank">+48 12 646 1216</a><br>
Mobile: <a href="tel:%2B48%20691%20464%20275" value="+48691464275" target="_blank">+48 691 464 275</a><br>
website: <a href="http://www.comarch.pl" target="_blank">www.comarch.pl</a><br>
<br>
<br>
</font></span><br>_______________________________________________<br>
erlang-patches mailing list<br>
<a href="mailto:erlang-patches@erlang.org">erlang-patches@erlang.org</a><br>
<a href="http://erlang.org/mailman/listinfo/erlang-patches" target="_blank">http://erlang.org/mailman/listinfo/erlang-patches</a><br>
<br></blockquote></div><br></div>