title: How do secondary indexes work in Cassandra?
body: <p>Suppose I have a column family:</p>
  <pre><code>CREATE TABLE update_audit (
    scopeid bigint,
    formid bigint,
    time timestamp,
    record_link_id bigint,
    ipaddress text,
    user_zuid bigint,
    value text,
    PRIMARY KEY ((scopeid, formid), time)
    ) WITH CLUSTERING ORDER BY (time DESC)
  </code></pre>
  <p>With two secondary indexes, where <code>record_link_id</code> is a high-cardinality column:</p>
  <pre><code>CREATE INDEX update_audit_id_idx ON update_audit (record_link_id);
  
  CREATE INDEX update_audit_user_zuid_idx ON update_audit (user_zuid);
  </code></pre>
  <p>According to my knowledge Cassandra will create two hidden column families like so:</p>
  <pre><code>CREATE TABLE update_audit_id_idx(
      record_link_id bigint,
      scopeid bigint,
      formid bigint,
      time timestamp
      PRIMARY KEY ((record_link_id), scopeid, formid, time)
  );
  
  CREATE TABLE update_audit_user_zuid_idx(
      user_zuid bigint,
      scopeid bigint,
      formid bigint,
      time timestamp
      PRIMARY KEY ((user_zuid), scopeid, formid, time)
  );
  </code></pre>
  <p>Cassandra secondary indexes are implemented as local indexes rather than being distributed like normal tables. Each node only stores an index for the data it stores.</p>
  <p>Consider the following query:</p>
  <pre><code>select * from update_audit where scopeid=35 and formid=78005 and record_link_id=9897;
  </code></pre>
  <ol>
  <li>How will this query execute 'under the hood' in Cassandra?</li>
  <li>How will a high-cardinality column index (<code>record_link_id</code>) affect its performance?</li>
  <li>Will Cassandra touch all nodes for the above query? <em>Why?</em></li>
  <li>Which criteria will be executed first, base table partition_key or secondary index partition_key? How will Cassandra intersect these two results?</li>
  </ol>