如何在 RethinkDB 中使用 getall 和 orderby [英] How to use getall with orderby in RethinkDB

问题描述

我想列出两个时间戳之间 id=1 的记录，最后根据时间戳对它们进行排序.

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

Mysql 查询的东西:

Mysql query something:

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

rethink 数据库中有超过 500 万个文档.

there are more than 5 million documents in rethink database.

我尝试将索引用于简单的 mysql 查询:

I tried using index for the simple mysql query:

Select * from test where id=1 order by timestamp

和 Rethinkdb 查询是:

and Rethinkdb query is:

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

但我收到错误:

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"})
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^

有什么建议吗?

推荐答案

RethinkDB 不支持高效的索引交集(Github issue 添加这个是 #809)，但是您可以通过为id"和timestamp"索引添加复合索引来有效地实现此查询.

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.

如果你的结果集足够小，那么 orderBy 可以通过删除 'index' optarg 完全在内存中完成:

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")

要对大型结果集有效地执行此操作，您需要一个索引.假设您的 'id' 和 '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")];
                            })

要获取 id=1 的所有行并按时间戳排序，您可以运行:

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"})

此外，回到您发布的原始查询，您可以通过运行在 id=1 的两个时间戳之间进行查询:

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"})

这篇关于如何在 RethinkDB 中使用 getall 和 orderby的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！