How does 'LIMIT' parameter work in sql?
Solution 1
From MySQL Reference Manual:
If you use LIMIT row_count with ORDER BY, MySQL ends the sorting as soon as it has found the first row_count rows of the sorted result, rather than sorting the entire result. If ordering is done by using an index, this is very fast. If a filesort must be done, all rows that match the query without the LIMIT clause must be selected, and most or all of them must be sorted, before it can be ascertained that the first row_count rows have been found. In either case, after the initial rows have been found, there is no need to sort any remainder of the result set, and MySQL does not do so.
So it looks like it's possible that the entire result set is known before the LIMIT
is applied. But MySQL will try everything it can not to do so. And you can help it by providing useful indexes that match your queries.
EDIT: Furthermore, if the set is not sorted it terminates the SELECT
operation as soon as it's streamed enough rows to the result set.
Solution 2
SELECT * FROM your_table
LIMIT 0, 10
This will display the first 10 results from the database.
SELECT * FROM your_table
LIMIT 5, 5
This will show records 6, 7, 8, 9, and 10
It's like telling MySql;
I want you to start counting from 5+1 or the 6th record, but Select only upto 5 records
Solution 3
I'm assuming you're thinking about MySQL, in which according to the documentation, the answer is it depends. If you're using a LIMIT
(without a HAVING
), then:
- If you are selecting only a few rows with
LIMIT
, MySQL uses indexes in some cases when normally it would prefer to do a full table scan. - As soon as MySQL has sent the required number of rows to the client, it aborts the query unless you are using
SQL_CALC_FOUND_ROWS
.
There are a few other cases which you should read about in the documentation.
Solution 4
It stops after it found the number of rows specified in the LIMIT clause. This can be verified with a large amount of data. It retrieves the result in a time that is not possible if it is getting all the rows of the table and filtering after that.
Solution 5
Introduction to MySQL LIMIT clause
The following illustrates the LIMIT clause syntax with two arguments:
SELECT
select_list
FROM
table_name
LIMIT [offset,] row_count;
- The
offset
specifies the offset of the first row to return. The offset of the first row is 0, not 1. - The
row_count
specifies the maximum number of rows to return.
The following picture illustrates the LIMIT clause:
Therefore, these two clauses are equivalent:
> LIMIT row_count;
> LIMIT 0 , row_count;
The following picture illustrates the evaluation order of the LIMIT clause in the SELECT statement:
Related videos on Youtube
Luis
Updated on July 09, 2022Comments
-
Luis almost 2 years
I have 4000 rows for example, and I define X limit.
The query stops after it finds X rows? or the query finds all the rows and then takes X rows from the found rows?
Thank you.
-
Daniel DiPaolo almost 13 years
LIMIT
is not a SQL standard keyword, so which RDBMS are you using?
-
-
Yuck almost 13 yearsI think the OP is asking about the technical implementation, not how to write the query.
-
ceejayoz almost 13 yearsIf he's asking about LIMIT, he's quite clearly not in MSSQL, as it doesn't have LIMIT.
-
Yuck almost 13 yearsIn an unordered query. If you're asking for explicit ordering it's possible (though unlikely) that it will sort the entire set before it's reached the limit requested.
-
Willa over 8 yearsThis does not really answer the question. The question clearly asks how mysql treats the LIMIT close and not what it does with it.
-
ErickBest over 8 years@willa Please re-Read the Question. it says: How does 'LIMIT' parameter work in sql? notice the word: Work ... And my answer show how it works ... And please let me have my UpVotes back. Thank You! :)
-
Willa over 8 yearsI still think you are not answering the question. Read both the title and the body.
-
ErickBest over 8 yearsConsider this: I have 4000 rows for example, and I define X limit. =>> ((catch this line)) <<== The query stops after it finds X rows? or the query =>> ((and this line)) finds all the rows and then takes X rows from the found rows? ....MY_ANSWER Simply says that: if Given:
SELECT * FROM your_table LIMIT 0, 10 // MySql will return 10 rows starting from 0
and inSELECT * FROM your_table LIMIT 5, 5 //MySql will Return rows Starting from the 6th & 7th, 8th, 9th, and 10th
... How doesn't this ANSWER Help the User? .... (some how) I feel Bullied by your comments -
Willa about 8 yearsSorry ErickBest I am in no way trying to bully you. I was looking for an explanation of how LIMIT works and honestly your explanation didn't give any more knowledge than I already have. I have re-read the question and your answer sorry but no thank you, you are not answering.
-
Willa about 8 yearsLook at your answer, it says what mysql will return given LIMIT clause without clarifying whether mysql fetches all matching rows and then pick the corresponding rows constrained by LIMIT or it simply fetch the records and when it hits the desired number of records asked by LIMIT it stops, which is what the question is asking. Look at the accepted answer, it states clearly what yours lacks! Sorry pal it is the question not me.
-
ErickBest about 8 yearsPeace Pal!... Somehow, My Answer illustrates the Accepted Answer if you salad both you get one great and Supreme Answer ...... that is why this answer has helped many with the same issue that have visited this page.