Need advice for data schema, DateTime ranges processing.

[Alexandr R. Ogurtzoff]

Hello gentlemen,

I would ask your advice what is the best way to operate Log like records in Redis. Say I have a set of records to store:
A typical record contains follow fields:
 - Unique ID (int);

- Some value (string);

- Status(int);

- Start Date (datetime);

- End Date (datetime)

so as we can see I'm going to save continuing events, each event has date of start and date of finish. At a first glance  I have to use following schema:

Save all records as individual hash:( $foo means a value of a filed)
HMSET "prefix:records:$ID"  ID $ID  Value $Value  Status $Status StartDate $StartDate EndDate $EndDate

To search by Status value save them in sets

SADD "prefix:Status:$Status" $ID

And add the ID into Start Dates' and End Dates' sorted sets:

ZADD "prefix:StarDate" $StartDate  $ID

ZADD "prefix:EndDate" $EndDate $ID

So what is not problem in this case to find events by Status or ID, my problem is how can I search for event by date?

I need to find events which were happening at some date. The way I see is:
 

Get the list of event's IDs which start before the given date:

    ZRANGEBYSCORE  prefix:StartDate -inf "$date"

Save them to temporary set:

 SADD tmp1 <IDs from the query above>

Get the list of event's IDs which finished after the given date and save them into another  temporary set:

  ZRANGEBYSCORE  prefix:EndDate  "$date" +inf

   SADD tmp2 <IDs from the query >

Intersect both sets:

 SINTER  tmp1 tmp2 => $list

And get the records from prefix:records:$id

$list.each  do |ID|

HGETALL prefix:records:$ID 

end

 

Something like this, but I guess it will take long time to serach by this way, how can I change dataschema or search procedure to have  Redis's profit?

I use 2.4.4, so no Lua available until it not in stable branch

Thank you in advance,   

--
My best wishes.
Alexandr Ogurtsov.

Linux is very friendly it is just picky who its friends are

[Dvir Volk]

Hello good sir,

Another suggestion for you:

If you keep one sorted set containing both startdates and enddates, you can do a range query easily.

you save each event twice, once with its start and once with its end date timestamp as score:

ZADD events $startDate {$id}_start

ZADD events $endDate {$id}_end

when you do a ZRANGEBYSCORE you'll get all events in this range, and only have to filter out the duplicates by replacing _start|_end with '' and getting the id.

this will use exactly the same amount of memory, but will enable you, with a bit of post processing work, to do this whole thing with 2 queries.

--
You received this message because you are subscribed to the Google Groups "Redis DB" group.
To post to this group, send email to redi...@googlegroups.com.
To unsubscribe from this group, send email to redis-db+u...@googlegroups.com.
For more options, visit this group at http://groups.google.com/group/redis-db?hl=en.

--

Dvir Volk

System Architect, DoAT, http://doat.com