title: Cassandra composite key queries
body: <p>This is more of a design question. </p>
  
  <p>Let's imagine I have this table (most columns omitted for readability):</p>
  
  <pre><code>CREATE TABLE IF NOT EXISTS users (
  userid uuid,
  emailaddress text,
  passwordhash text,
  passwordsalt text,
  datecreated timestamp,
  PRIMARY KEY (userid, emailaddress)
  );
  </code></pre>
  
  <p>On this table, I need to sometimes get userid by emailaddress and other times, simply use the userid to get the rest of the values, so the queries are:</p>
  
  <ol>
  <li>Search by <code>emailaddress</code>, get <code>userid</code> and use in other places</li>
  <li>Search by <code>userid</code>, get all the values for specific user and use that elsewhere.</li>
  </ol>
  
  <p>Querying by only <code>userid</code> works since it's the first key in the composite key table but for querying by <code>emailaddress</code> (not knowing the userid in advance), I will have to turn on the <code>ALLOW FILTERING</code> which is strongly discouraged.</p>
  
  <p>Question is: knowing this design is not good in terms of how Cassandra operates, how else would I achieve my goal (to get user details by EITHER <code>userid</code> OR <code>emailaddress</code>)? Should I create 2 tables such as these?</p>
  
  <pre><code>CREATE TABLE IF NOT EXISTS users (
  userid uuid,
  passwordhash text,
  passwordsalt text,
  datecreated timestamp,
  PRIMARY KEY (userid)
  );
  
  CREATE TABLE IF NOT EXISTS useremails (
  emailaddress text,
  userid uuid,
  PRIMARY KEY (emailaddress)
  );
  </code></pre>
  
  <p>Or is there a cleaner way to achieve what I want to do? I apologize in advance for my lack of understanding the ins and outs of Cassandra. I'm still learning it and trying to approach the problems from the correct angle instead of hacking (which <code>ALLOW FILTERING</code> would allow me to do).</p>
  
  <p>Many thanks,</p>