ALLOW FILTERING implementation @Cassandra

[hemabs]

Hi,

I have a table like below: CREATE TABLE tab( categoryid text, id text, name text, author text, desc text, PRIMARY KEY (categoryid , id) ) WITH CLUSTERING ORDER BY (id ASC) CREATE INDEX ON tab (name); CREATE INDEX ON tab (author);

When I execute the below queries: select * from tab ALLOW FILTERING;---1 select * from tab where id = 'id01' ALLOW FILTERING;---2 select * from tab where categoryid = 'cid01' ALLOW FILTERING;---3

What is happening in the back end for the three queries?

Is it going to completely neglect the key indices on id and categoryid.

Appreciate the reply. Thanks

[Olivier Michallat]

Hi,

Your partition key is categoryid and id is a clustering column.

Query 1 returns everything in the table so it won't take advantage of any index. You don't need "allow filtering" here since you asked for all data anyway (which doesn't mean that it's a good idea to run this query in production, querying all partitions will be costly).

Query 2 restricts on the clustering column but not on the partition id. Cassandra will need to retrieve all partitions and check each id, this means unpredictable performance and "allow filtering" is required.

Query 3 retrieves a specific partition. You don't need "allow filtering" here.

See also this article: http://www.datastax.com/dev/blog/allow-filtering-explained-2

--

Olivier Michallat

Driver & tools engineer, DataStax

To unsubscribe from this group and stop receiving emails from it, send an email to java-driver-us...@lists.datastax.com.

[hemabs]

Thanks Olivier. Incase I include ALLOW FILTERING in the production code on all below queries; would it blindly query all partitions.

Or is it that the backend is intelligent enough to figure out like on first query and third query not to query on all partitions; Just apply ALLOW FILTERING on the second query.

Or is this something that the cassandra client should be handling based on the use case scenario.

Thanks

[Alex Popescu]

On Thu, Mar 5, 2015 at 3:54 PM, hemabs <hbal...@hawk.iit.edu> wrote:

Incase I include ALLOW FILTERING in the production code

You should never do that.

--

[:>-a)

Alex Popescu
Sen. Product Manager @ DataStax
@al3xandru

[hemabs]

Ok. Thanks Olivier.