title: MongoDB find slow with regex
body: <p>The system I currently develop on, uses MongoDB 2.4.4</p>
  
  <p>I have a collection of users.</p>
  
  <p>There is a combined index: <code>{ "LASTNAME" : 1 , "FIRSTNAME" : 1 , "EMAIL" : 1 , "CITY" : 1 , "STATUS" : 1}</code>
  I also tried with single indexes, without a performance increase</p>
  
  <p>The system contains 400.000 test records.</p>
  
  <p>The query (Java debug from org.springframework.data.mongodb.core.query.Query): </p>
  
  <pre><code>{ "LASTNAME" : { "$regex" : "^Schm"}},
    Fields: { "FIRSTNAME" : 1 , "EMAIL" : 1 , "CITY" : 1 , "STATUS" : 1 ,"LASTNAME" : 1},
    Sort: { "LASTNAME" : 1 , "FIRSTNAME" : 1 , "EMAIL" : 1 , "CITY" : 1 , "STATUS" : 1}
  </code></pre>
  
  <p>performs within 16ms.That is fantastic.</p>
  
  <p>This query is not shown in the MongoDB console (no debug information to post here).</p>
  
  <p>But, I like to search not only starts with, it also should be case insensitive.</p>
  
  <p>The query:</p>
  
  <pre><code>{ "LASTNAME" : { "$regex" : "^Schm" , "$options" : "i"}},
    Fields: { "FIRSTNAME" : 1 , "EMAIL" : 1 , "CITY" : 1 , "STATUS" : 1 , "LASTNAME" : 1},
    Sort: { "LASTNAME" : 1 , "FIRSTNAME" : 1 , "EMAIL" : 1 , "CITY" : 1 , "STATUS" : 1}
  </code></pre>
  
  <p>performs within 897ms. That is unacceptable slow.</p>
  
  <p>Mongo Console shows this:</p>
  
  <pre><code>query: { query: { LASTNAME: /^Schm/i },
    orderby: { LASTNAME: 1, FIRSTNAME: 1, EMAIL: 1, CITY:1, STATUS: 1 }
  } cursorid:1252405545564528 ntoreturn:25 ntoskip:0 nscanned:297651
  keyUpdates:0 numYields: 1 locks(micros) r:1391715 nreturned:25 reslen:4422 897ms
  </code></pre>
  
  <p>As one can see. It's not the scanAndOrder problem that points onto index problems.</p>
  
  <p>Then I tried to solve it the next way what fits the most scenarios (inserted from the user, lower case and upper case), but that is slower also.
  My expectation was, that it performs thrice as long as the first query.</p>
  
  <p>The query:</p>
  
  <pre><code>{ "$or" : [ { "LASTNAME" : { "$regex" : "^Schm"}} , { "LASTNAME" : { "$regex" : "^schm"}} , { "LASTNAME" : { "$regex" : "^SCHM"}}]},
    Fields: { "FIRSTNAME" : 1 , "EMAIL" : 1 , "CITY" : 1 , "STATUS" : 1 , "LASTNAME" : 1},
    Sort: { "LASTNAME" : 1 , "FIRSTNAME" : 1 , "EMAIL" : 1 , "CITY" : 1 , "STATUS" : 1}
  </code></pre>
  
  <p>performs within 1300ms. Nothing more to say.</p>
  
  <p>MongoDB console:</p>
  
  <pre><code>query: { query: { $or: [ { LASTNAME: /^Schm/ }, { LASTNAME: /^schm/ }, { LASTNAME: /^SCHM/ } ] },
    orderby: { LASTNAME: 1, FIRSTNAME: 1, EMAIL: 1, CITY: 1, STATUS: 1 }
  } cursorid:43560166842085 ntoreturn:25 ntoskip:0 nscanned:297651
  keyUpdates:0 numYields: 1 locks(micros) r:1531168 nreturned:25 reslen:4422 1300ms
  </code></pre>
  
  <p>So, how can I search case insensitive that nearly has the speed of the first search?
  Maximal 150ms!</p>