mnesia:select

[Evans, Matthew]

Big speed up by changing the key from

content_uuid = RealUuid

to a tuple

content_uuid = {RealUuid,IndexFile},

-----Original Message-----
From: erlang-questions@REDACTED [mailto:erlang-questions@REDACTED] On Behalf Of Evans, Matthew
Sent: Thursday, June 18, 2009 3:58 PM
To: erlang-questions@REDACTED
Subject: [erlang-questions] mnesia:select

Hi,

I've asked a similar question before, but no takers.

I have the following mnesia query:

=============

    Guard = [{'>=','$5',Offset},{'<','$5',Offset+UpperLimit}],
    Match = #mpeg_index_data{
            content_uuid = RealUuid,
            sub_file_name = IndexFile,
            index_byte_offset = '$4',
            normal_rate_packet_offset = '$5',
            other_data = '$6'},

    Result = ['$4','$5','$6'],
    Transaction = fun() -> mnesia:select(mpeg_index_data,[{Match, Guard, [Result]}]) end,

    case mnesia:activity(async_dirty, Transaction, [], mnesia_frag) of
    ....

=============

content_uuid is the primary key of the record, and sub_file_name is an index. The table is fragmented and type "bag" and the table is created with a secondary index:

mnesia:create_table(mpeg_index_data, [{frag_properties,[{node_pool, [node()]},{n_fragments,4},{n_disc_copies,0}, {n_disc_only_copies,0}]},
                                                  {index,[sub_file_name]},{record_name,mpeg_index_data},{attributes, record_info(fields,mpeg_index_data)},{type, bag}]).


First a generic question. Does mnesia:select use both the primary key and the secondary index in the search? Or even with indexes does it do a full-table search?

Second question is how could I make the searches faster? Even with a secondary index the search can be quite slow on large tables. I thought of adding a third index (called 'quotient' see below) which will provide a ball-park for the search. But even with this the searches can be quite slow (plus I would need to replicate some records). The issue is the PacketOffset will rarely fall on a known value.

=============

    Guard = [{'>=','$5',Offset},{'<','$5',Offset+UpperLimit}],
    Match = #mpeg_index_data{
            content_uuid = RealUuid,
            sub_file_name = IndexFile,
            quotient = round(PacketOffset/1000),
            index_byte_offset = '$4',
            normal_rate_packet_offset = '$5',
            other_data = '$6'},

    Result = ['$4','$5','$6'],
    Transaction = fun() -> mnesia:select(mpeg_index_data,[{Match, Guard, [Result]}]) end,

    case mnesia:activity(async_dirty, Transaction, [], mnesia_frag)
    ....

=============

Any suggestions would be appreciated.

Thanks

Matt