title: Fast search on a blob starting bytes in SQLite
body: <p>Is there a way to index blob fields and have the index used for beginning of blob searches?</p>
  
  <p>Currently I have hashes stored as hexadecimal in text fields.
  These hashes in hexadecimal form are 32 characters long, and form the bulk of the data in the database.</p>
  
  <p>Problem is, they are often searched by their starting bytes, as in</p>
  
  <pre><code>select * from mytable where hash like '00a1b2%'
  </code></pre>
  
  <p>I would like to store them as blobs, as this saves about 30% of the database size. However while</p>
  
  <pre><code>select * from mytable where hex(hash) like '00a1b2%'
  </code></pre>
  
  <p>works, it's also much slower and does not seem to use the index.</p>
  
  <p>Searching for exact blob matches does use the index, so the index is working.</p>
  
  <p>Is there a way to perform a search on a blob start (with binary/memcmp "collation") that would use the index?</p>
  
  <p>I also tried substr(), it's apparently faster than hex() but still not indexed</p>
  
  <pre><code>select * from mytable where substr(hash, 1, 6) = x'00a1b2'
  </code></pre>