Hi Varun, Thanks a lot for your reply.
In this case if I want to update status(status can be updated for given account_id, pid) , I need to delete existing row in 2nd table & add new one... :( :( Its like hitting cassandra twice for 1 change.. :( On Sun, Jul 23, 2017 at 8:42 PM, Varun Barala <varunbaral...@gmail.com> wrote: > Hi, > > You can create pseudo index table. > > IMO, structure can be:- > > > CREATE TABLE IF NOT EXISTS test.user ( account_id bigint, pid bigint, > disp_name text, status int, PRIMARY KEY (account_id, pid) ) WITH CLUSTERING > ORDER BY (pid ASC); > CREATE TABLE IF NOT EXISTS test.user_index ( account_id bigint, pid bigint, > disp_name text, status int, PRIMARY KEY ((account_id, status), disp_name) ) > WITH CLUSTERING ORDER BY (disp_name ASC); > > > to support query *:- select * from site24x7.wm_current_status where > uid=1 order by dispName asc;* > You can use *in condition* on last partition key *status *in table > > *test.user_index.* > > > > *It depends on your use case and amount of data as well. It can be > optimized more...* > Thanks!! > > On Sun, Jul 23, 2017 at 2:48 AM, techpyaasa . <techpya...@gmail.com> > wrote: > >> Hi , >> >> We have a table like below : >> >> CREATE TABLE ks.cf ( accountId bigint, pid bigint, dispName text, status >>> int, PRIMARY KEY (accountId, pid) ) WITH CLUSTERING ORDER BY (pid ASC); >> >> >> >> We would like to have following queries possible on the above table: >> >> select * from site24x7.wm_current_status where uid=1 and mid=1; >> select * from site24x7.wm_current_status where uid=1 order by dispName >> asc; >> select * from site24x7.wm_current_status where uid=1 and status=0 order >> by dispName asc; >> >> I know first query is possible by default , but I want the last 2 queries >> also to work. >> >> So can some one please let me know how can I achieve the same in >> cassandra(c*-2.1.17). I'm ok with applying indexes etc, >> >> Thanks >> TechPyaasa >> > >
