Is it safe to use LIMIT without ORDER BY

It depends what you consider as safe- if you want consistent result (meanning, getting the same result everytime as long as the table's content will not change) or if you want specific result (biggests, newest, oldest, whatever)- than this requires order. If by safety you meam that the query wont crush, and you dont care which X results you get- than yes, using limit is fine (this is actually done automatically by many sql tools, like mysql workbench, to speed things up).

In terms of speed- will make it faster without order for two reasons:

  1. ordering takes time.
  2. Using limit allow the server to stop as it finds the first X results. You can see that queries with limit 10 will run faster than limit 100000 on large tables. When you use order, the server must go through all result, so cant stop in the middle.

So yes, using limit without order will make it faster


If you are not using the ORDER BY then you are not sorting your records, so it will definitely make your data retrieval faster. So if you simply use LIMIT then it would be faster as compared to the data retrieved through ORDER BY. But do note that, in that case the data will not be in any order.

As far as the safety is concerned, I am not sure about which safety you are thinking of, as there is no potential harm in a query which uses only LIMIT and does not uses an ORDER BY clause.

You can also look at the article: ORDER BY … LIMIT Performance Optimization

Tags:

Mysql

Innodb