Reputation: 13192
View v_one
Query 1 : SELECT id FROM v_one LIMIT 2
: 52 ms
Query 2 : SELECT id FROM v_one ORDER BY id LIMIT 2
: 2413 ms
View v_two
Query 3 : SELECT id FROM v_two LIMIT 2
: 260 ms
Query 4 : SELECT id FROM v_two ORDER BY id LIMIT 2
: 260 ms
Why on the view v_one, when using order by, the time difference so far?
Any help much appreciated
Cheers
Upvotes: 0
Views: 72
Reputation: 1
The query engine first sorts the data so it takes longer if there is no index. The limit restricts the results after sorting data.
Hope this helps.
Upvotes: 0
Reputation: 48207
Beside the Index as GordonLinoff say, table size can affect the time need to order the data. So be carefull when you compare queryes.
ORDER BY
so just take first two elementsORDER BY
need time, specially if the table is bigger and not index for id
.Last you can use EXPLAIN ANALYZE to compare query plan between both querys
EXPLAIN ANALYZE
SELECT id FROM v_one LIMIT 2
Upvotes: 2