Is it safe to use LIMIT without ORDER BY

Question

I'm using InnoDB. I need to query to get 10 records from a table is any order.

Is it safe to use LIMIT without ORDER BY? Would it be faster?

Answer 1

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

Answer 2

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

Answer 3

It's not safe.

Without a order by, the results may not be consistent over consecutive excutions of the same query.

Refer to mysql limit collapse, which result in data interaction

For example:(course_id is the primay key).

Get the first page 10 rows;

select course_id,grade_id from sc_base_course where  agency_id = 10000  limit 0,10;
+-----------+----------+
| course_id | grade_id |
+-----------+----------+
|        13 |        1 |
|         6 |        3 |
|        12 |        4 |
|         8 |        2 |
|         7 |        2 |
|         9 |        4 |
|        16 |        1 |
|         1 |        2 |
|        17 |        1 |
|        14 |        5 |
+-----------+----------+

Get the second page 7 rows

select course_id,grade_id from sc_base_course where  agency_id = 10000  limit 10,10;
+-----------+----------+
| course_id | grade_id |
+-----------+----------+
|        11 |        4 |
|        12 |        4 |
|        13 |        1 |
|        14 |        5 |
|        15 |        1 |
|        16 |        1 |
|        17 |        1 |
+-----------+----------+

Answer 4

Yes, you can and yes, it would be faster (assuming the order does not matter to you). order by requires sorting. This means the database has to do more work to get you the result. Most commonly limit is used with order by since want to put some ordering constraints on which 10 records you get (say most recent, highest rank of some sort, etc.)

Answer 5

The 3 answers so far are good. But they are not totally correct.

Contrary to the other answers, sometimes leaving out ORDER BY will not make it faster. The optimizer might happen to generate the rows in that order anyway. Examples:

• GROUP BY usually orders the results. So, if the ORDER BY would match the GROUP BY, there is no extra effort.
• ORDER BY the_primary_key might match the order of fetching. This is almost guaranteed to be the case for InnoDB without a WHERE clause.

"Safe" does not compute.