Cassandra where clause for specific column - best approach

Question

I need to use where clause while searching for records with particular PID in my cassandra database

id = uuid
pid=  Property Id (text)
created_at = timestamp

I need to find the top 5 records of a particular property Id. So my create table looks like this.

CREATE TABLE property_tax (
    id uuid,
    state text, 
    area text,       
    balance_type text,
    created_at timestamp,
    created_by text,
    last_paid_at timestamp,
    max_tax float,
    min_tax float,
    pid text,
    prev_balance float,
    prev_interest float,
    property_type text,
    tax_cess float,
    tax_year timestamp,
    total_paid float,
    total_paid_cess float,
    total_paid_tax float,
    PRIMARY KEY (pid,created_at,id)
    );

and my query looks like this

select * from property_tax where pid = 'property1' ORDER BY created_at DESC LIMIT 5;

Its working as per my requirement but is my approach correct? or does it need to change. Is there any performance issue which can arise in future. I am looking at 500 million records and growing.

NEW EDIT:

I have added two column 1.state 2.area There will be multiple areas in the state

Property ID(pid) will have multiple records not more than 100 records

So, I need to query TABLE property_tax for below
1. Find all the pid
2. find all the pid in the area
3. find all the pid in the state
4. find Limit 5 for pid (ORDER_BY created_at DESC)

Many Thanks Shashi

Answer 1

If your always going to make the query that way add WITH CLUSTERING ORDER BY (created_at DESC); so you don't need to reverse order read (a bit more efficient). But thats a good query for that table.

Given its 500 million pid's you mean that will work well. If its 500 million id's within a single pid you might end up with a very wide partition which has performance impacts.