Mailing-List: contact user-help@cassandra.apache.org; run by ezmlm
Precedence: bulk
Reply-To: user@cassandra.apache.org
Received-SPF: pass (nike.apache.org: domain of michael.laing@nytimes.com
 designates 209.85.216.44 as permitted sender)
MIME-Version: 1.0
In-Reply-To: 
 <CA+7gEciNCruD=N+wfwWE-Gvw5c5ZFJjnWi5qeaNMM8tqp9s3fg@mail.gmail.com>
References: 
 <CA+7gEciNCruD=N+wfwWE-Gvw5c5ZFJjnWi5qeaNMM8tqp9s3fg@mail.gmail.com>
Date: Fri, 1 Aug 2014 05:34:30 -0700
Message-ID: 
 <CAKgmDnHkY6FDWd3LO5VJVTd86n5m6xd5mAiWYGeh05ToJL7YmA@mail.gmail.com>
Subject: Re: select many rows one time or select many times?
From: "Laing, Michael" <michael.laing@nytimes.com>
To: user@cassandra.apache.org
Content-Type: multipart/alternative; boundary=001a11c02a8e5a3d7d04ff909ca4

--001a11c02a8e5a3d7d04ff909ca4
Content-Type: text/plain; charset=UTF-8

I don't think there is an easy "answer" to this...

A possible approach, based upon the implied dimensions of the problem,
would be to maintain a bloom filter over "words" for each user as a
partition key with the user as clustering key. Then a single query would
efficiently yield the list of users that "may" match and other techniques
could be used to refine that list down to actual matches.

ml


On Thu, Jul 31, 2014 at 10:44 AM, Philo Yang <ud1937@gmail.com> wrote:

> Hi all,
>
> I have a cluster of 2.0.6 and one of my tables is like this:
> CREATE TABLE word (
>   user text,
>   word text,
>   flag double,
>   PRIMARY KEY (user, word)
> )
>
> each "user" has about 10000 "word" per node. I have a requirement of
> selecting all rows where user='someuser' and word is in a large set whose
> size is about 1000 .
>
> In C* document, it is not recommended to use "select ... in" just like:
>
> select from word where user='someuser' and word in ('a','b','aa','ab',...)
>
> So now I select all rows where user='someuser' and filtrate them via
> client rather than via C*. Of course, I use Datastax Java Driver to page
> the resultset by setFetchSize(1000).  Is it the best way? I found the
> system's load is high because of large range query, should I change to
> select for only one row each time and select 1000 times?
>
> just like:
> select from word where user='someuser' and word = 'a';
> select from word where user='someuser' and word = 'b';
> select from word where user='someuser' and word = 'c';
> .....
>
> Which method will cause lower pressure on Cassandra cluster?
>
> Thanks,
> Philo Yang
>
>

--001a11c02a8e5a3d7d04ff909ca4
Content-Type: text/html; charset=UTF-8
Content-Transfer-Encoding: quoted-printable

<div dir=3D"ltr">I don&#39;t think there is an easy &quot;answer&quot; to t=
his...<div><br></div><div>A possible approach, based upon the implied dimen=
sions of the problem, would be to maintain a bloom filter over &quot;words&=
quot; for each user as a partition key with the user as clustering key. The=
n a single query would efficiently yield the list of users that &quot;may&q=
uot; match and other techniques could be used to refine that list down to a=
ctual matches.</div>
<div><br></div><div>ml</div></div><div class=3D"gmail_extra"><br><br><div c=
lass=3D"gmail_quote">On Thu, Jul 31, 2014 at 10:44 AM, Philo Yang <span dir=
=3D"ltr">&lt;<a href=3D"mailto:ud1937@gmail.com" target=3D"_blank">ud1937@g=
mail.com</a>&gt;</span> wrote:<br>
<blockquote class=3D"gmail_quote" style=3D"margin:0 0 0 .8ex;border-left:1p=
x #ccc solid;padding-left:1ex"><div dir=3D"ltr">Hi all,<div><br></div><div>=
I have a cluster of 2.0.6 and one of my tables is like this:</div><div><div=
>CREATE TABLE word (</div>
<div>=C2=A0 user text,</div><div>=C2=A0 word text,</div><div>=C2=A0 flag do=
uble,</div><div>=C2=A0 PRIMARY KEY (user, word)</div>
<div>)</div></div><div><br></div><div>each &quot;user&quot; has about 10000=
 &quot;word&quot; per node. I have a requirement of selecting all rows wher=
e user=3D&#39;someuser&#39; and word is in a large set whose size is about =
1000 .=C2=A0</div>

<div><br></div><div>In C* document, it is not recommended to use &quot;sele=
ct ... in&quot; just like:</div><div><br></div><div>select from word where =
user=3D&#39;someuser&#39; and word in (&#39;a&#39;,&#39;b&#39;,&#39;aa&#39;=
,&#39;ab&#39;,...)=C2=A0</div>

<div><br></div><div>So now I select all rows where user=3D&#39;someuser&#39=
; and=C2=A0filtrate them via client rather than via C*. Of course, I use Da=
tastax Java Driver to page the resultset by setFetchSize(1000). =C2=A0Is it=
 the best way? I found the system&#39;s load is high because of large range=
 query, should I change to select for only one row each time and select 100=
0 times?</div>

<div><br></div><div>just like:</div><div>select from word where user=3D&#39=
;someuser&#39; and word =3D &#39;a&#39;;</div><div>select from word where u=
ser=3D&#39;someuser&#39; and word =3D &#39;b&#39;;<br></div><div>select fro=
m word where user=3D&#39;someuser&#39; and word =3D &#39;c&#39;;<br>

</div><div>.....</div><div><br></div><div>Which method will cause lower pre=
ssure on Cassandra cluster?</div><div><br clear=3D"all"><div><div dir=3D"lt=
r"><div><span style=3D"font-family:arial,sans-serif;background-color:rgb(25=
5,255,255)">Thanks,</span>
</div><div>Philo Yang</div><div><br></div></div></div>
</div></div>
</blockquote></div><br></div>

--001a11c02a8e5a3d7d04ff909ca4--