Design Query Optimization for Cassandra Table with Date-based Filtering

[arjun s]

Hello team, I'm new to Cassandra and I've created a table using the
following command:

CREATE TABLE instant_cdr (
  rowid int,
  msisdn varchar,
  id bigint,
  date_id int,
  channel varchar,
  usage_type varchar,
  ac_type varchar,
  voice_amt int,
  sms_amt int,
  data_amt int,
  field1 varchar,
  field2 varchar,
  field3 varchar,
  field4 varchar,
  field5 varchar,
  field6 varchar,
  field7 varchar,
  field8 varchar,
  field9 varchar,
  field10 varchar,
  field11 varchar,
  field12 varchar,
  field13 varchar,
  field14 varchar,
  field15 varchar,
  field16 varchar,
  field17 varchar,
  field18 varchar,
  field19 varchar,
  field20 varchar,
  s_date date,
  date_idp int,
  PRIMARY KEY (rowid, msisdn, date_id, id)
);



In this table, rowid, msisdn, and date_id may have repeated values, so I
added an id column with an auto-incremental value for unique entries.
However, my concern is that queries will be based on the date_id. For
example:

SELECT VOICE_AMT, SMS_AMT, DATA_AMT FROM instant_cdr WHERE rowid = 0 and
msisdn = '8010000000' and date_id >= 231202 AND date_id <= 231204;



The issue is that I can only apply >= and <= on the id column, which is not
used in the query. Is there a method to execute the query without
mentioning ALLOW FILTER in the query, or what would be the best approach to
design the table for this use case?

Regards,
Arjun