How to use getall with orderby in RethinkDB

Question

i want to list records where id=1 between two timestamps and finally order them according to timestamp.

Mysql query something:

Select * from test 
where (timestamp between 100099323 AND 1423699323) AND id=1 
order by timestamp

there are more than 5 million documents in rethink database.

I tried using index for the simple mysql query:

Select * from test where id=1 order by timestamp

and Rethinkdb query is:

r.table('test').getAll(1, {index: 'id'}).orderBy({index: 'timestamp'})

but i am getting error:

RqlRuntimeError: Indexed order_by can only be performed on a TABLE or 
TABLE_SLICE in:
r.table("test").getAll(1, {index: "id"}).orderBy({index: "timestamp"})
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^

Any suggestion?

Answer 1

RethinkDB doesn't support efficient index intersection (the Github issue to add this is #809), but you could implement this query efficiently by adding a compound index for the 'id' and 'timestamp' indexes.

If your result set is small enough, though, the orderBy could just be done completely in-memory by dropping the 'index' optarg:

r.table("test").getAll(1, {index: "id"}).orderBy("timestamp")

To do this efficiently for large result sets, you would need an index. Assuming your 'id' and 'timestamp' indexes correspond directly to fields in your rows, adding the index would look like:

r.table("test").indexCreate("id_time",
                            function(row) {
                                return [row("id"), row("timestamp")];
                            })

To get all the rows with id=1 and sort by the timestamp, you would then run:

r.table("test").between([1], [2], {"index": "id_time"})
               .orderBy({"index": "id_time"})

In addition, going back to the original query you posted, you could query between two timestamps for id=1 by running:

r.table("test").between([1, <time 1>], [1, <time 2>], {"index": "id_time"})
               .orderBy({"index": "id_time"})