ALLOW FILTERING usage

[Kasper Middelboe Petersen <ka...@sybogames.com>]

Hi,

I have a table:

CREATE TABLE json (
       key text,
       group text,
       date timestamp,
       json text,
       PRIMARY KEY((key, group), date)
) WITH CLUSTERING ORDER BY (date DESC);

This table will contain a small amount of data (only what an administrator
creates by hand - a year from now maybe 6 different keys, 10-15 groups for
a total of 60-90 rows each with up to maybe 15 columns).

I need the application to detect changes to this and was planning to poll
the table every few minutes for new content (while its not often its
updated, the update needs to go live fairly quick).

My question then is how big, if any, a difference there would be to doing:
SELECT * FROM json WHERE date < :now: AND date > :lastCheck: ALLOW FILTERING
to
SELECT * FROM json WHERE key IN (<key1...key6>) AND group IN
(<group1...group15>) AND date < :now: AND date > :lastCheck:


/Kasper

Re: ALLOW FILTERING usage

["Laing, Michael" <mi...@nytimes.com>]

Your second query is invalid:

*Bad Request: Partition KEY part key cannot be restricted by IN relation
(only the last part of the partition key can)*

ml


On Mon, Mar 17, 2014 at 6:56 AM, Tupshin Harper <tu...@tupshin.com> wrote:

> It's the difference between reading from only the partitions that you are
> interested, vs reading every single partition before filtering the
> results.  At scale, and assuming you don't actually need to read every
> partition, there would be a huge difference.
>
> If the model requires you to read every partition in the table in the IN
> approach, then there probably won't be much difference.
>
> In general, I would prefer the approach where you explicitly specify the
> keys you are needing to poll.
>
> -Tupshin
> On Mar 17, 2014 6:51 AM, "Kasper Middelboe Petersen" <ka...@sybogames.com>
> wrote:
>
>> Hi,
>>
>> I have a table:
>>
>> CREATE TABLE json (
>>        key text,
>>        group text,
>>        date timestamp,
>>        json text,
>>        PRIMARY KEY((key, group), date)
>> ) WITH CLUSTERING ORDER BY (date DESC);
>>
>> This table will contain a small amount of data (only what an
>> administrator creates by hand - a year from now maybe 6 different keys,
>> 10-15 groups for a total of 60-90 rows each with up to maybe 15 columns).
>>
>> I need the application to detect changes to this and was planning to poll
>> the table every few minutes for new content (while its not often its
>> updated, the update needs to go live fairly quick).
>>
>> My question then is how big, if any, a difference there would be to doing:
>> SELECT * FROM json WHERE date < :now: AND date > :lastCheck: ALLOW
>> FILTERING
>> to
>> SELECT * FROM json WHERE key IN (<key1...key6>) AND group IN
>> (<group1...group15>) AND date < :now: AND date > :lastCheck:
>>
>>
>> /Kasper
>>
>

Re: ALLOW FILTERING usage

[Tupshin Harper <tu...@tupshin.com>]

It's the difference between reading from only the partitions that you are
interested, vs reading every single partition before filtering the
results.  At scale, and assuming you don't actually need to read every
partition, there would be a huge difference.

If the model requires you to read every partition in the table in the IN
approach, then there probably won't be much difference.

In general, I would prefer the approach where you explicitly specify the
keys you are needing to poll.

-Tupshin
On Mar 17, 2014 6:51 AM, "Kasper Middelboe Petersen" <ka...@sybogames.com>
wrote:

> Hi,
>
> I have a table:
>
> CREATE TABLE json (
>        key text,
>        group text,
>        date timestamp,
>        json text,
>        PRIMARY KEY((key, group), date)
> ) WITH CLUSTERING ORDER BY (date DESC);
>
> This table will contain a small amount of data (only what an administrator
> creates by hand - a year from now maybe 6 different keys, 10-15 groups for
> a total of 60-90 rows each with up to maybe 15 columns).
>
> I need the application to detect changes to this and was planning to poll
> the table every few minutes for new content (while its not often its
> updated, the update needs to go live fairly quick).
>
> My question then is how big, if any, a difference there would be to doing:
> SELECT * FROM json WHERE date < :now: AND date > :lastCheck: ALLOW
> FILTERING
> to
> SELECT * FROM json WHERE key IN (<key1...key6>) AND group IN
> (<group1...group15>) AND date < :now: AND date > :lastCheck:
>
>
> /Kasper
>