SQL Query becomes incredibly slow when ordering empty set
<p>I have an SQL query that takes lots of time to evaluate because it is operating on a very big dataset. When trying to improve the execution time I found out the following:</p>

<p>When executing the following query the MySQL server takes a lot of time (up to 100secs)</p>

<pre><code>SELECT some_data 
FROM   table 
       INNER JOIN anothertable 
               ON ( table.value = 
                               anothertable.value ) 
WHERE  ( table.parent = 56521 
         AND table.date &gt;= 
             '2016-10-19 08:37:45.606947' ) 
ORDER  BY table.date DESC 
LIMIT  1
</code></pre>

<p>So I guessed that it's the sorting part of the query that takes so much execution time and I manually removed to sorting to see the differences in the execution:</p>

<pre><code>SELECT some_data 
FROM   table 
       INNER JOIN anothertable 
               ON ( table.value = 
                               anothertable.value ) 
WHERE  ( table.parent = 56521 
         AND table.date &gt;= 
             '2016-10-19 08:37:45.606947' ) 
LIMIT  1
</code></pre>

<p>The query above takes 0.45 secs and leads to an empty query set.</p>

<p>I came to the conclusion that my query orders the WHOLE data set before evaluating the WHERE-Clause. How should I form the query in order to prevent that behaviour? Why does this behaviour show up?</p>

<p>These are the EXPLAIN Tables for the slow and the fast query:</p>

<pre><code>Slow
+----+-------------+-------+------------+--------+------------------------------------------+------------------+---------+------------------------------+------+----------+-------------+
    | id | select_type | table | partitions | type   | possible_keys                            | key              | key_len | ref                          | rows | filtered | Extra       |
    +----+-------------+-------+------------+--------+------------------------------------------+------------------+---------+------------------------------+------+----------+-------------+
    |  1 | SIMPLE      | A     | NULL       | index  | PRIMARY,D4b797d14e515242e7251754c57b7701 | date             | 5       | NULL                         | 1325 |     0.08 | Using where |
    |  1 | SIMPLE      | B     | NULL       | eq_ref | PRIMARY                                  | PRIMARY          | 4       | value                        |    1 |   100.00 | NULL        |
    +----+-------------+-------+------------+--------+------------------------------------------+------------------+---------+------------------------------+------+----------+-------------+

Fast:
     +----+-------------+-------+------------+--------+------------------------------------------+----------------------------------+---------+------------------------------+------+----------+-------+
    | id | select_type | table | partitions | type   | possible_keys                            | key                              | key_len | ref                          | rows | filtered | Extra |
    +----+-------------+-------+------------+--------+------------------------------------------+----------------------------------+---------+------------------------------+------+----------+-------+
    |  1 | SIMPLE      | A     | NULL       | ref    | PRIMARY,D4b797d14e515242e7251754c57b7701 | D4b797d14e515242e7251754c57b7701 | 4       | const                        | 5175 |   100.00 | NULL  |
    |  1 | SIMPLE      | B     | NULL       | eq_ref | PRIMARY                                  | PRIMARY                          | 4       | value                        |    1 |   100.00 | NULL  |
    +----+-------------+-------+------------+--------+------------------------------------------+----------------------------------+---------+------------------------------+------+----------+-------+
</code></pre>