[jira] [Created] (CASSANDRA-11144) Allow indexes on materialized views

[Jon Haddad (JIRA)]

Jon Haddad created CASSANDRA-11144:
--------------------------------------

             Summary: Allow indexes on materialized views
                 Key: CASSANDRA-11144
                 URL: https://issues.apache.org/jira/browse/CASSANDRA-11144
             Project: Cassandra
          Issue Type: Bug
            Reporter: Jon Haddad


Users should be able to create indexes on fields in materialized views.  Here's 
an example of a user database.  It would be good to be able to efficiently 
query the database for users born on a particular day with a particular name 
(or name starting with a certain letter).  I've used SASI indexes since they 
support LIKE prefixes.

{code}
CREATE TABLE users (
    id uuid PRIMARY KEY,
    first_name text,
    last_name text,
    birthday date
);

CREATE MATERIALIZED VIEW users_by_date AS
    SELECT birthday, id, first_name, last_name
    FROM users
    WHERE birthday IS NOT NULL
    PRIMARY KEY (birthday, id)
    WITH CLUSTERING ORDER BY (id ASC);

CREATE CUSTOM INDEX on users_by_date(first_name) 
    USING 'org.apache.cassandra.index.sasi.SASIIndex';
    {code}

And I get back

{code}
InvalidRequest: code=2200 [Invalid query] message="Secondary indexes are not 
supported on materialized views"
{code}

I think the index is valid, since it'll allow me to take advantage of querying 
a single partition, and the index allows me to find arbitrary rows within that 
partition.



--
This message was sent by Atlassian JIRA
(v6.3.4#6332)