[erlang-questions] mnesia range queries

[Paul Mineiro]

Simplest and most efficient ... at the same time? :)

Unless creation_date is (a prefix of) the key in an ordered_set table,
this will involve a full table scan.  In the example below field_a is
the key so a full table scan will happen.  You really want records that
look like

{ foo, creation_date, ... } or
{ foo, { creation_date, ... }, ... }

So this might require making another table mapping creation_date to the
primary key in your original table.  This is what relational DB engines
do for you when you make an index.

-- p

On Wed, 17 Jun 2009, Evans, Matthew wrote:

>
> Something like this should work, assuming the operation is dirty, and your date is expressed as epoch time:
>
>     Guard = [{'>=','$2',Date}],
>     Match = #foo{
>             field_a = '$1',
>             creation_date = '$2',
>             other_data = '$3',
>             more_data = '$4'},
>     Result = ['$1,'$2','$3','$4'],
>     mnesia:dirty_select(foo,[{Match, Guard, [Result]}],10,read),
>
> The problem can often be that select with a range (that's what the 10,read does) is slow. You might be better doing:
>
>     Guard = [{'>=','$2',Date},{'<','$2',Date+UpperLimit}],
>     Match = #foo{
>             field_a = '$1',
>             creation_date = '$2',
>             other_data = '$3',
>             more_data = '$4'},
>     Result = ['$1,'$2','$3','$4'],
>     mnesia:dirty_select(foo,[{Match, Guard, [Result]}]),
>
> Where upper limit does the work of the select 10 results. I've not tested this code, but the principle should work (I've only used select, not dirty_select before, but they should work the same).
>
> If you want to further qualify the search you can assign values/keys to the fields in the Match record structure.
>
> The result should be a list of lists, you might need to write a simple function that'll sort the returned values if they aren't in order (I don't know what'll happen if you make the date a key in the table creation function, if it'll try to sort them for you automatically).
>
> Matt
> ________________________________________
> From: erlang-questions@REDACTED [erlang-questions@REDACTED] On Behalf Of Yariv Sadan [yarivvv@REDACTED]
> Sent: Wednesday, June 17, 2009 2:28 AM
> To: erlang-questions
> Subject: [erlang-questions] mnesia range queries
>
> Hi,
>
> What's the simplest, most efficient way of expressing the following
> sql query in mnesia:
>
> "SELECT * FROM foo ORDER BY creation_date LIMIT 500,10"
>
> ?
>
> Btw, I don't care about transactions.
>
> Thanks,
> Yariv
>
> ________________________________________________________________
> erlang-questions mailing list. See http://www.erlang.org/faq.html
> erlang-questions (at) erlang.org
>
>
> ________________________________________________________________
> erlang-questions mailing list. See http://www.erlang.org/faq.html
> erlang-questions (at) erlang.org
>
>