How does 'LIMIT' parameter work in sql?

I have 4000 rows for example, and I define X limit.

The query stops after it finds X rows? or the query fi开发者_如何转开发nds all the rows and then takes X rows from the found rows?

Thank you.

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.

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

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.

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:

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.

If you are using MS SQL Server, then you can write it as given below.

Select TOP [x]

*
From MyTable

Hope it helps.

Vamyip