title: Validating row at client side better than secondary index with whole primary key?
body: <p>In cassandra, it's well known that secondary indexes should be used very sparingly.</p>
  
  <p>If I have a table for example:</p>
  
  <pre><code>User(username, usertype, email, etc..)
  </code></pre>
  
  <p>Here username is partition key. Now I want to support operation which returns a specific user(username will be given) if and only if usertype is a specific value X.</p>
  
  <p>There are two ways I can do it:</p>
  
  <p>One:
  Create a secondary index on usertype, possible values ('A', 'B', 'C')
  and username is partition key.</p>
  
  <pre><code>SELECT * FROM user WHERE username='something' AND usertype='A';
  </code></pre>
  
  <p>Two:</p>
  
  <p>I can just fetch the row with username to client and then check if usertype is A.</p>
  
  <p><strong>Which approach is better?</strong> Please also consider a wide row(not so big, 10s) scenario where not all rows of a partition might have the given value (which requires some client side filtering).</p>
  
  <p>What I'm not clear about secondary indexes is how data is looked up in a particular node. </p>
  
  <p>Ex: <code>SELECT * FROM user WHERE username='something' AND usertype='A'</code></p>
  
  <p>For example usertype hidden CF has data 'A'-> 'jhon', 'miller', 'chris',...etc, 100 usernames </p>
  
  <p>And the query with partition key is given along with usertype does it scan through all these 100 usernames to match with the username 'something' or does it just fetches by username first and sees the usertype column if it matches with 'A'? <strong>How exactly it does that searching? How does the query fares  given the index is on low cardinality data and each one is mapped to many rows?</strong></p>
  
  <p>I'm using java as client if that matters.</p>
  
  <p><strong>Update:</strong>
  I understand that I can use clustering (usertype) key for this particular example but I wanted to know the trade off I've asked. My original tables are much more complex.</p>