title: Large Sqlite database search
body: <p>How is it possible to implement an efficient large Sqlite db search (more than 90000 entries)?</p>
  
  <p>I'm using Python and SQLObject ORM:</p>
  
  <pre><code>    import re
      ...
  
      def search1():
          cr = re.compile(ur'foo')
          for item in Item.select():
              if cr.search(item.name) or cr.search(item.skim):
                  print item.name
  </code></pre>
  
  <p>This function runs in more than 30 seconds. How should I make it run faster? </p>
  
  <p><strong>UPD</strong>: The test:</p>
  
  <pre><code>    for item in Item.select():
          pass
  </code></pre>
  
  <p>... takes almost the same time as my initial function (0:00:33.093141 to 0:00:33.322414). So the regexps eat no time.</p>
  
  <p>A Sqlite3 shell query:</p>
  
  <pre><code>    select '' from item where name like '%foo%';
  </code></pre>
  
  <p>runs in about a second. So the main time consumption happens due to the inefficient ORM's data retrieval from db. I guess SQLObject grabs entire rows here, while Sqlite touches only necessary fields.</p>