title: Database Queries: Fast alternatives to Django's order_by()
body: <p>I've been teaching myself Django and SQL, and one thing I've noticed is that when working with large tables (> 1,000,000 records), specifying an ordering is painfully slow. For example:</p>
  
  <p><code>Model.objects()[offset:limit]</code></p>
  
  <p>might take a few milliseconds, assuming <code>offset</code> and <code>limit</code> are a small enough range. However:</p>
  
  <p><code>Model.objects()[offset:limit].order_by('name')</code></p>
  
  <p>Could take 10 or 20 seconds, depending on the number of rows in the table. I understand why this is happening; all rows must be checked to ensure that the correct results are returned. I also understand that this is more an SQL problem and not a Django problem, it's just easier for me to explain it with Django code. </p>
  
  <p>So these are my questions:</p>
  
  <ol>
  <li><p>Since I see Django production websites display ordered data from extremely large tables, how do they achieve this without each query taking >10 seconds?</p></li>
  <li><p>After I solve the first question, how could I extend my Django application to allow ordering of multiple columns (name, date, value, etc)? </p></li>
  </ol>
  
  <p>My intuition says that the answer to the first question is to insert each record in the order I want it displayed so that no ordering is necessary when performing queries, but that seems difficult to maintain.</p>
  
  <p>Also, that means that the only answer I can think of to the second question involves creating multiple tables that are all ordered by different columns, and then accessing it like this:</p>
  
  <pre><code>if request.GET['order'] == name:
      result_set = NameOrderedModel.objects()[offset:limit]
  elif request.GET['order'] == value:
      result_set = ValueOrderedModel.objects()[offset:limit]
  </code></pre>
  
  <p>The code for these queries is rather simple, but I shudder to think of how much work it would take to set up a database so that queries like this are possible. Is there a better way?</p>