Hey. I have a problem creating a materialized view. My case is quite similar to https://issues.apache.org/jira/browse/CASSANDRA-13564 but discussion in comments there faded, let me describe by case.
I have a table like CREATE TABLE users ( site_id int, user_id text, n int, data set<frozen<text>>, PRIMARY KEY ((site_id, user_id), n)); user data is updated and read by PK and sometimes I have to fetch all user for some specific site_id. It appeared that full scan by token(site_id,user_id) filtered by WHERE site_id = <some id> works much slower than unfiltered full scan on CREATE MATERIALIZED VIEW users_1 AS SELECT site_id, user_id, n, data FROM users WHERE site_id = 1 AND user_id IS NOT NULL AND n IS NOT NULL PRIMARY KEY ((site_id, user_id), n); yes, you have to do so for each site_id, but it makes such bulk fetches much faster. (When I do so, I am always puzzled, why I have to put NOT NULL for a part of a primary key). And just in case, I tried secondary indices on site_id. For such use they improve nothing. But things are changing and we realized that we want to get rid of clustering key, n. DROP MATERIALIZED VIEW users_1; DROP TABLE users; CREATE TABLE users ( site_id int, user_id text, data set<text>, PRIMARY KEY ((site_id, user_id))); CREATE MATERIALIZED VIEW users_1 AS SELECT site_id, user_id, data FROM users WHERE site_id = 1 AND user_id IS NOT NULL PRIMARY KEY ((site_id, user_id)); And here I get the error I listed in the subject. InvalidRequest: Error from server: code=2200 [Invalid query] message="No columns are defined for Materialized View other than primary key" But why? I still expect scans to be faster with MV. It appears to be possible to create a dummy column and using as a clustering key. That's ugly. -- Best Regards, *Alexander Kotelnikov* *Team Lead* DIGINETICA Retail Technology Company m: +7.921.915.06.28 *www.diginetica.com <http://www.diginetica.com/>*
