title: What prefix to use when indexing SecureRandom string column
body: <p>I have a DB column (type <code>varchar(255)</code>) that stores a URL-safe base 64 string produced by <code>SecureRandom.urlsafe_base64</code>. The method call uses the default values, so the result should be <a href="https://apidock.com/ruby/SecureRandom/urlsafe_base64/class" rel="nofollow noreferrer">16 bytes or <strong>22 characters</strong> in length</a>.</p>
  
  <p>The base64 value is used to look up records when a user hits the site, to mask DB IDs. I need an index for this column because of this lookup, however I don't want to index the entire column because that <em>could</em> be inefficient storage-wise.</p>
  
  <p>What's the best approach to determining the optimal index prefix to use in this case? What I'm thinking right now is something like this:</p>
  
  <ol>
  <li>Create sample data with about 100k records to simulate production data</li>
  <li>Add an index for the base 64 column with a prefix (say 8 characters)</li>
  <li>Run <code>EXPLAIN</code> on a lookup on the base 64 column to see how many
  records need to be inspected</li>
  <li>Adjust the index up or down and re-do step 3.</li>
  <li>Choose a prefix size that balances (a) storage requirements and (b) number of records returned with matching hits.</li>
  </ol>
  
  <p>The issue here is that I know <code>SecureRandom</code> produces unique base 64 strings, but I'm not sure <em>how unique</em> they are. Eg, out of 100k records, if I use a prefix of 8 characters, will that prefix be shared by 10 records or 100?</p>
  
  <p>Some more specific questions about my approach:</p>
  
  <ol>
  <li>Is 100k records enough of a sample to pick a good prefix size?</li>
  <li>If I did apply an index <em>without</em> using a prefix, is my suspicion that this is bad storage-wise correct?</li>
  <li>What's a reasonable number of records to expect to have to query the table directly, and still benefit from an index?</li>
  </ol>
  
  <p><strong>Notes</strong>:</p>
  
  <ul>
  <li>My database is MySQL (actually Percona)</li>
  <li><code>SecureRandom</code> comes from Ruby</li>
  <li>I'm assuming that the URL-safe feature of <code>SecureRandom</code> isn't changing the uniqueness characteristics of the base 64 output.</li>
  </ul>