I have a question about LIMIT/TOP. As I understand before we get only rows from the limit, the whole table is processed. so if I write Select * from TABLE limit 2, first the whole table is processed and then it is cut. Is there a way to cut it before it gets processed? So for example "take 2 random rows". So then I don't query the whole table, but only a part of it. I hope this question makes sense to you. I will appreciate your help!

CodePudding user response：

MySQL Limit clause used select statement is used to restrict the number of rows returns from the result set, rather than fetching the whole set from table.

If you use "Select * from TABLE limit 2" it will give result set in random order. it better to used Limit clause with criteria so you can increase the performance on table.

For Ex: SELECT * FROM TABLE WHERE column_name >30
ORDER BY column_name DESC
LIMIT 5;

CodePudding user response：

In the execution plan tree a LIMIT node will stop processing the child nodes as soon as it's complete; i.e., when it receives the maximum number of rows from the child nodes (in your case 2 rows).

This will be very effective in terms of performance and response time if the child nodes are pipelined, reducing the cost drastically. For example:

select * from t limit 2;

If the child nodes are materialized then the subbranch will be entirely processed before limiting, and the cost and response time won't be significantly affected. For example:

select * from t order by rand() limit 2;