title: Even with indices this query is relatively slow. How to speed it up?
body: <p>I have a table called videos. It's got more columns than this but it should be enough for this example.</p>
  <pre><code>+----+-------------+------------+----------+--------+
  | ID | Title       | Date       | Quality  | Length |
  +----+-------------+------------+----------+--------|
  |  1 | video title | 2001-01-01 | 720      | 50     |
  +----+-------------+------------+----------+--------+
  </code></pre>
  <p>I want to be able to sort and filter by almost anything in this table. So I created an index for every sortable column and a compound index for the columns you can filter by. It works great until i want to sort and filter at the same time.</p>
  <p>in this query it  uses the compound index and it's all good. query takes 0.003 seconds</p>
  <pre><code>select * 
  from videos
  where quality = 720
  and length = 50
  limit 20
  </code></pre>
  <p>here it uses the date index. works great. query takes 0.003 seconds</p>
  <pre><code>select * 
  from videos
  order by Date desc
  limit 20
  </code></pre>
  <p>Here it's using the date index. query takes 1.3 seconds</p>
  <pre><code>select * 
  from videos
  where quality = 720
  and length = 50
  order by Date desc
  limit 20
  </code></pre>
  <p>So, obviously it's because it sorts by date then checks every row until it finds 20 matching rows. Sometimes it will be fast and sometimes slow if it needs to go through thousands and thousands of rows until it finds matching rows.</p>
  <p>But how can this be sped up? Is caching the only solution here? Or is there something else I can do?</p>
  <p>Edit: If i hint to use the compound index it's still slow. I'm guessing it's because it still needs to be sorted before going through them row by row.</p>