SQL query performance difference

Question

I have a table with 1million record and i designed 2queries.

table index:

id = primary
bedroom = index
elevator = index
pricemeter = index
dateadd = index

I want to know why this query:

SELECT 
    *
FROM (
    SELECT 
        * 
    FROM `p_apartmentbuy` 
    WHERE
        `sqm` > 100
        AND `bedroom` > 1
        AND `elevator` = 1
        AND `pricemeter` < 5999999
    ORDER BY `id` DESC
    LIMIT 0, 30
) AS `alias`
ORDER BY `id` DESC, `dateadd` DESC

Is so much faster than this one:

SELECT 
    * 
FROM `p_apartmentbuy` 
WHERE
    `sqm` > 100
    AND `bedroom` > 1
    AND `elevator` = 1
    AND `pricemeter` < 5999999
ORDER BY `id` DESC, `dateadd` DESC
LIMIT 0, 30

First query took 0.0027 sec and second query took 5.6848 sec. Both of the result are the same as other and the variables on where clause are example.

EXPLAIN for fast query:

EXPLAIN for slow query:

Answer 1

Well, we need more information about indexes, but a simple look at those queries show that they are different. They might throw different results too, its just a coincidence that they are the same (most likely based on how your data is structured). On the first query you are choosing 30 rows, ordering just by id (wich might have an index), and then it sorts them by dateadd, wich is pretty simple. Your second query has to sort all your million records by those two columns, and then choose 30, so it clearly will be more expensive.

Answer 2

Second query has to sort the whole dataset by id and dateadd before it can apply limit.

The first query, on the other hand, returns early with just 30 records, which are then sorted by id and dateadd. Much less work.