Mailing-List: contact commits-help@cassandra.apache.org; run by ezmlm
Precedence: bulk
Reply-To: dev@cassandra.apache.org
Date: Wed, 10 Feb 2016 03:52:18 +0000 (UTC)
From: "Jon Haddad (JIRA)" <jira@apache.org>
To: commits@cassandra.apache.org
Message-ID: <JIRA.12938072.1455076283000.12108.1455076338083@Atlassian.JIRA>
In-Reply-To: <JIRA.12938072.1455076283000@Atlassian.JIRA>
References: <JIRA.12938072.1455076283000@Atlassian.JIRA>
 <JIRA.12938072.1455076283356@arcas>
Subject: [jira] [Created] (CASSANDRA-11144) Allow indexes on materialized
 views
MIME-Version: 1.0
Content-Type: text/plain; charset=utf-8
Content-Transfer-Encoding: 7bit

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)