1

As far as I understand there are no secondary indices with multiple columns, only single columns.

So how do I solve the index problem I'm having? This is my table:

CREATE TABLE IF NOT EXISTS logstv.messages ( timestamp timestamp, channelid bigint, userid bigint, message text, PRIMARY KEY ((channelid, timestamp), userid) ) WITH CLUSTERING ORDER BY (timestamp DESC);

But this is the error currently: Missing CLUSTERING ORDER for column userid

I want these things:

  • SELECT WHERE with channelid, userid, timestamp with an order by timestamp and limit

  • SELECT WHERE with channelid, timestamp with an order by timestamp and limit

Is this possible?

The only option I currently see is setting timestamp as the primary key and then sorting through the rest in my software, which seems very expensive. Or having 2 tables for my data which i want to very much avoid because the data will grow very fast and get big.

Example queries:

SELECT message, timestamp FROM logstv.messages WHERE userid = ? AND channelid = ? AND timestamp >= ? AND timestamp <= ? ORDER BY timestamp DESC LIMIT 100; SELECT message, timestamp FROM logstv.messages WHERE channelid = ? AND timestamp >= ? AND timestamp <= ? ORDER BY timestamp DESC LIMIT 100;
Improve this question
1
  • The general solution is to duplicate the data in another table with a different key. Indexes are possible but very inefficient. Commented Apr 30, 2023 at 12:27

3 Answers 3

Reset to default
1

Yeah, It is possible with 'ALLOW FILTERING' option. BUT NOT ADVISABLE (well explained in this link). Schema design in Cassandra is majorly Query Specific (i.e) design your schema so that it is able to answer a query.

If you want to query on different attributes, go for individual schemas for each one.

Improve this answer
13
  • Is it maybe possible without ALLOW FILTERING; but only supporting WHERE timestamp + channelid and timestamp + userid + channelid ? Leaving out some queries? Commented Sep 24, 2018 at 14:19
  • @gempir as per your model in the question, you can query only based on channel.id + user.id /// or /// channel.id+user.id+timestamp and nothing more than that . Commented Sep 24, 2018 at 14:45
  • The schema is changeable. I just want to avoid storing messages twice in 2 tables Commented Sep 24, 2018 at 14:51
  • What kind of queries do you want to apply on logstv.messages? Commented Sep 24, 2018 at 14:58
  • 2 kind something with channelid, timestamp and userid AND a query with just channelid and timestamp Commented Sep 24, 2018 at 15:09
1

The proper way to solve this for Cassandra is to model two separate tables, one to support each query.

For this query:

SELECT message, timestamp FROM logstv.messages WHERE userid = ? AND channelid = ? AND timestamp >= ? AND timestamp <= ? ORDER BY timestamp DESC LIMIT 100;

...this table should work:

CREATE TABLE IF NOT EXISTS logstv.messages ( timestamp timestamp, channelid bigint, userid bigint, message text, PRIMARY KEY ((channelid), userid, timestamp) ) WITH CLUSTERING ORDER BY (userid ASC, timestamp DESC);

The idea here, is that timestamp has to be the last clustering column in order for you to be able to specify a range condition on it.

For this query:

SELECT message, timestamp FROM logstv.messages WHERE channelid = ? AND timestamp >= ? AND timestamp <= ? ORDER BY timestamp DESC LIMIT 100;

I'd build a query table like this:

CREATE TABLE IF NOT EXISTS logstv.messages_by_channelid ( timestamp timestamp, channelid bigint, userid bigint, message text, PRIMARY KEY ((channelid), timestamp, userid) ) WITH CLUSTERING ORDER BY (timestamp DESC, userid ASC);

Same approach, but userid and timestamp should be flipped.

The only potential problem I see, is that depending on how many messages are received per-channel, you may want to partition on a date component...just not timestamp. Not sure how many messages this system receives, but perhaps month would work?

Ex:

PRIMARY KEY ((channelid, month), timestamp, userid)

You would then have to specify the month of the messages that you would like to get back. Another time component may be more appropriate, but that's the general idea.

Improve this answer
0

I cannot see the need to put timestamp into the partition key. You can check out partitioning in Cassandra

In case you want to query a column that is not in your current table's primary key you have the Materialized View to do so for ONE other column (per view). Cassandra 3.0

Improve this answer

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.