Mailing-List: contact user-help@cassandra.apache.org; run by ezmlm
Precedence: bulk
Reply-To: user@cassandra.apache.org
Received-SPF: pass (athena.apache.org: domain of cernoso@gmail.com designates
 209.85.223.177 as permitted sender)
MIME-Version: 1.0
Date: Fri, 24 May 2013 16:52:09 +0200
Message-ID: 
 <CAAgSJSXEe7b53SuKWtFFeTDJEs+M4T2PrODLOh=ykvnVgO43OA@mail.gmail.com>
Subject: pagination in cql3
From: =?UTF-8?B?T25kxZllaiDEjGVybm/FoQ==?= <cernoso@gmail.com>
To: user@cassandra.apache.org
Content-Type: multipart/alternative; boundary=089e0116058280b80e04dd77f19d

--089e0116058280b80e04dd77f19d
Content-Type: text/plain; charset=ISO-8859-1

Hi all,

I need to support a legacy API where page offset and limit are on the input
of the API call (it used to be mapped directly to offset and limit MySQL
select options). The data are pretty small (like really small,
some hundreds of thousands narrow rows maximum - I use Cassandra for its
multiple-dc and HA capabilities, not for "big data").

I know the token(key) function and its use for paging, but unfortunately I
cannot change the API to a version where last key on previous page and
limit would be provided.

What I thought I would do - though it is violating good Cassandra practices
like "don't fetch all keys"  - is the following:

select _key_ from table limit _offset_value_;
select _columns_ from table where token(_key_) >
token(_last_key_from_the_select_above_);

The first select tells me where the offset begins and the second one
queries for the page. The paged queries will not be performed too often, so
performance is not such a big deal here.

This construct however depends on repeatable ordering of keys returned from
the select key from table query. I don't care about the ordering, but I
need to know it is actually ordered by key tokens. Afaik it should be so
(SSTs are ordered this way, the coordinator merges the data from queried
nodes, ssts and memtables - I suppose it all preserves the order), but I
don't know if it really works this way and if it is "documented" so that I
can rely on it.

Or should it be done some other way?

Thanks,

Ondrej Cernos

--089e0116058280b80e04dd77f19d
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable

<div dir=3D"ltr">Hi all,<div><br></div><div style>I need to support a legac=
y API where page offset and limit are on the input of the API call (it used=
 to be mapped directly to offset and limit MySQL select options). The data =
are pretty small (like really small, some=A0hundreds=A0of thousands narrow =
rows maximum - I use Cassandra for its multiple-dc and HA capabilities, not=
 for &quot;big data&quot;).</div>
<div style><br></div><div style>I know the token(key) function and its use =
for paging, but unfortunately I cannot change the API to a version where la=
st key on previous page and limit would be provided.</div><div style><br>
</div><div style>What I thought I would do - though it is=A0violating=A0goo=
d Cassandra practices like &quot;don&#39;t fetch all keys&quot; =A0- is the=
 following:</div><div style><br></div><div style>select _key_ from table li=
mit _offset_value_;</div>
<div style>select _columns_ from table where token(_key_) &gt; token(_last_=
key_from_the_select_above_);</div><div style><br></div><div style>The first=
 select tells me where the offset begins and the second one queries for the=
 page. The paged queries will not be performed too often, so performance is=
 not such a big deal here.</div>
<div style><br></div><div style>This construct however depends on repeatabl=
e ordering of keys returned from the select key from table query. I don&#39=
;t care about the ordering, but I need to know it is actually ordered by ke=
y tokens. Afaik it should be so (SSTs are ordered this way, the coordinator=
 merges the data from queried nodes, ssts and memtables - I suppose it all =
preserves the order), but I don&#39;t know if it really works this way and =
if it is &quot;documented&quot; so that I can rely on it.</div>
<div style><br></div><div style>Or should it be done some other way?</div><=
div style><br></div><div style>Thanks,</div><div style><br></div><div style=
>Ondrej Cernos</div></div>

--089e0116058280b80e04dd77f19d--