Mnesia Top 1?

Allan Merolla allan.merolla@REDACTED
Fri Sep 4 02:07:34 CEST 2009

I have a massive table containing  time-series (values versus time) for different object. Table structure is like ObjectID, Time, Value. {ObjectID, Time} is the key.
I have a function that returns interpolated values (e.g. a value for each 10min) for a time range and object ID called getList(ObjId, FromTime, ToTime).

I use the following qlc query to get un-interpolated data.
qlc:q([X<-mnesia:table(Tbl), X#tbl.objId = ObjId andalso X#tbl.time>=FromTime andalso X#tbl.time<ToTime ]). Things work fine so far but the problem is that I need to know what had been the value for ObjId for a record just before FromTime and also a record just after ToTime.
I am new to mnesia, but in SQL the answer is:
SELECT TOP1 * FROM Tbl WHERE [ObjectID]=ObjID AND [Time]<FromTime ORDER BY [Time] DESC.

I know in sql if I put an index on the [Time] column, the above operation would be fast even in a massive table. The question is: What is the most efficient way to do the above top1 query in mnesia? I found that there is a function called prev(key) in mnesia that returns the key for the previous record but it won't help me since previous record may belong to another Object.

Best Regards//

Allan Merolla
Analyst Programmer

One Team, One Dream, Zero!
Incidents & Non Conformances
Level 2, 120 Wickham Street
Fortitude Valley QLD 4006
PO Box 141, Albion QLD 4010
Phone:  +61 7 3257 4711
Fax:      +61 7 3257 4788
Mobile:  0430 300 277
Notice: if you are not an authorised recipient of this e-mail, please contact the sender by return e-mail. In this case, you should not read, print, re-transmit, store, act or rely on this e-mail or any attachments, and should destroy all copies of them. This e-mail and attachments are confidential and may contain copyright material of the sender or other parties. You should only re-transmit, distribute or commercialise the material if you are authorised to do so. This notice should not be removed.

More information about the erlang-questions mailing list