1. 关于在Cassandra上添加索引的备忘录

考虑到使用物化视图，讨论如何创建Cassandra表
由于讨论尚在进行中，内容理应会有变化。

为了使用物化视图，需要至少创建最基本的索引。

将分类作为分区键，将UNIQ作为索引的最小设置。

CREATE TABLE IF NOT EXISTS example.basetable (
partition_id int,
item_code text,
seq_id bigint,
PRIMARY KEY((partition_genre_id), item_code)
);

通过将希望排序的关键字段设置为主键，可以实现按照order by进行排序。

create materialized view if not exists example.basetable_mv
as select * from basetable 
where item_code is not null
and seq_id is not null
and partition_genre_id is not null
primary key ((partition_genre_id), seq_id, item_code);

由于没有设置为主键，因此会出现错误。

SELECT 
item_code,
seq_id 
FROM example.basetable
where partition_genre_id = 211553 and seq_id > 0
order by seq_id limit 1000 ALLOW FILTERING;


cqlsh:mykeyspace> SELECT  item_code, seq_id  FROM example.basetable where partition_genre_id = 211553 and seq_id > 0 order by seq_id limit 3 ALLOW FILTERING;
InvalidRequest: Error from server: code=2200 [Invalid query] message="Order by is currently only supported on the clustered columns of the PRIMARY KEY, got seq_id"

通过将其重新设置为主键，可以实现对order by的排序。

SELECT 
item_code,
seq_id 
FROM example.basetable_mv
where partition_genre_id = 211553 and seq_id > 0
order by seq_id limit 1000 ALLOW FILTERING;


cqlsh:mykeyspace> SELECT  item_code, seq_id  FROM example.basetable_mv where partition_genre_id = 211553 and seq_id > 0 order by seq_id limit 3 ALLOW FILTERING;

 item_code        | seq_id
------------------+----------------
     XXX:11234299 | 15358979170100
     XX1:11789006 | 15358979170101
     XX2:11771190 | 15358979170103

(3 rows)