Welcome to OStack Knowledge Sharing Community for programmer and developer-Open, Learning and Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
824 views
in Technique[技术] by (71.8m points)

mysql - 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 finds all the rows and then takes X rows from the found rows?

Thank you.

See Question&Answers more detail:os

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Answer

0 votes
by (71.8m points)

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.


与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome to OStack Knowledge Sharing Community for programmer and developer-Open, Learning and Share
Click Here to Ask a Question

...