CODEWITHSUNDEEP
rethinkdb
Borgy Manotoy
Borgy Manotoy

Reputation: 2038

Rethink DB Query use secondary index for unix time stamp value range

I want to query or filter specific access logs for a give unix time stamps (start and end). However, using plain filter below takes forever to query. There are more than 1M records in this table.

Also, there is a secondary index unixTimestamp which I want to use so that it would be a lot faster. My problem is how to use that secondary index unixTimestamp and perform the query like below?

Rethink Query:

r.db("my_db")
  .table("my_access_log")
  .filter(
    function(accessLog) {
      return accessLog("unixTimestamp").ge(1550660220)
        .and(accessLog("unixTimestamp").le(1550663580));
    }
  )

Sample Data (Access log):

{
    "access":  "Granted" ,
    "createdDateS":  "2019-02-19T18:17:01" ,
    "id":  "abc123" ,
    "sn":  "123413523523" ,
    "unixTimestamp": 1550563149 ,
    "user": [email protected], »
}

Thanks!

Upvotes: 0

Views: 80

Answers (1)

Borgy Manotoy
Borgy Manotoy

Reputation: 2038

Sorry for posting the answer sooner, I needed the answer asap and found out myself... I just post it just in case someone would be having the same problem.

r.db("my_db")
  .table("my_access_log")
    .between(1550660220, 1550663580, {"index": "unixTimestamp"})
      .orderBy({index: r.desc('unixTimestamp')})

This is searching using secondary index unixTimestamp between unix time stamps 1550660220 (start) and 1550663580 (end). Then sort the results in descending order by the field unixTimestamp.

If you need to filter by date... just replace the between(start, end) values.

With this, I was able to get the records immediately compared if I will not use the secondary index as my table has almost 2M records.

Hope this helps!

Upvotes: 1

Related Questions