[
https://issues.apache.org/jira/browse/CASSANDRA-6477?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=14632464#comment-14632464
]
Jack Krupansky commented on CASSANDRA-6477:
-------------------------------------------
Are there any significant advantages or disadvantages of using an MV as a pure
global index - no data columns other than the primary key columns?
Consider the use case of large customer data rows with customer id as the
primary key, and you wish to log in by any of customer id, user id, email
address, social security number, full name and age or birth date, and name
alone, but you really want to simply immediately map any of those alternative
logins to the customer id so that the main customer data tables can be accessed
directly rather than having all of the data replicated in a bunch of MVs.
So, each of the four MVs would not need any non-PK data columns per se, since
the base table PK is (must be, right?) in the MV PK, I think. Does this make
sense? Would there be any special efficiency (or inefficiency) to having
essentially empty partitions? For example:
{code}
CREATE TABLE cust (id text, email text, ssn text, name text, address text, zip
text, birth timestamp, data map<text,text>, pwd text, PRIMARY KEY (id));
CREATE MATERIALIZED VIEW email AS SELECT id,email FROM cust PRIMARY KEY (email,
id);
CREATE MATERIALIZED VIEW ssn AS SELECT id,ssn FROM cust PRIMARY KEY (ssn, id);
CREATE MATERIALIZED VIEW name AS SELECT id,name FROM cust PRIMARY KEY (name,
id);
CREATE MATERIALIZED VIEW email AS SELECT id,name,zip,birth FROM cust PRIMARY
KEY ((name,zip,birth), id);
{code}
Incidentally, the lookup by name alone would not necessarily be unique - it
might not be for an end-user login per se but for a customer service agent who
would view the list and then ask the customer some questions to narrow down
which specific customer they are.
Does this specific use case represent what might be considered a best practice
use of MVs? If not, why not or what improvements could be made?
> Materialized Views (was: Global Indexes)
> ----------------------------------------
>
> Key: CASSANDRA-6477
> URL: https://issues.apache.org/jira/browse/CASSANDRA-6477
> Project: Cassandra
> Issue Type: New Feature
> Components: API, Core
> Reporter: Jonathan Ellis
> Assignee: Carl Yeksigian
> Labels: cql
> Fix For: 3.0 beta 1
>
> Attachments: test-view-data.sh, users.yaml
>
>
> Local indexes are suitable for low-cardinality data, where spreading the
> index across the cluster is a Good Thing. However, for high-cardinality
> data, local indexes require querying most nodes in the cluster even if only a
> handful of rows is returned.
--
This message was sent by Atlassian JIRA
(v6.3.4#6332)
