CODEWITHSUNDEEP
mysqlsqlpostgresql
moses toh
moses toh

Reputation: 13192

Why sql order by very slow?

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

Answers (2)

Mahesh
Mahesh

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

Juan Carlos Oropeza
Juan Carlos Oropeza

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.

  • Query1 doesnt have ORDER BY so just take first two elements
  • Query2 is slow because ORDER BY need time, specially if the table is bigger and not index for id.
  • Query3 and Query4 even when different has same time probably because this view has index or the table is small.

Last you can use EXPLAIN ANALYZE to compare query plan between both querys

EXPLAIN ANALYZE
SELECT id FROM v_one LIMIT 2

Upvotes: 2

Related Questions