Clever Geek Handbook

SQL performance difference

I have a table with 1 million records and I have worked out 2 queries.

table index:

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

I want to know why this request:

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

It's 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

The first request took 0.0027 seconds, and the second request took 5.6848 seconds. Both results are the same as the others and examples of where variables.

EXPLAIN for quick query: enter image description here

EXPLAIN for a slow query: enter image description here

+3


source to share


4 answers


Well, we need more information about the indexes, but a simple look at these queries shows that they are different. They can also give different results, just coincidence that they are the same (most likely based on how your data is structured). In the first query, you select 30 rows, ordering only id

(which may have an index), and then sort them by dateadd

, which is pretty simple. Your second query should sort all of your millionth records with those two columns and then select 30, so this is clearly more expensive.



+2


source


The second query must sort the entire dataset id

and dateadd

before it can apply the constraint.



The first query, on the other hand, returns first with 30 records, which are then sorted by id

and dateadd

. Much less work.

+2


source


An explanation will confirm

But the first one gets the first 30 records by id, which is presumably the primary key, and therefore Order by id does not require sorting. Then sorting only those 30.

Whereas the second one, gets all the records in order, then sorts them by id and date, and then takes the first 30 ...

Index on dateadd maybe?

An educated guess only though.

+1


source


Your table should use the ID as the primary key. In this case, the rows are indexed by the ID column and all data is already ordered by ID.

So the first 30 lines are fast for the mysql plan. then order those 30 lines to be quick.

The second query should return all rows to be sorted according to the 'dateadd' column. The LIMIT clause is then evaluated.

Hope this answers your question.

0


source






More articles:


All Articles