How valid are requests without LIMIT?

Question

PHP

Is there a clear explanation somewhere, good / bad practice about requests without limits?
And then there are people who make requests at the output of which an array of data is expected, do not limit the output, and as a result, the scripts fall with the message that the memory has run out.
And also, for clarity, how to hang up the server if I know the URL that gives out without LIMIT data of 10x MB per client?

Answer 1

Advantages of using limit:
1) Limitation and predictability of the number of resulting rows from the server side,
2) Less network load,
3) It is easier to design a GUI by issuing the result of a query in "packs" with a certain number of rows.
4) A hint for the query optimizer, which will lead to an increase in fetch performance on the server side
1) You can break the logic and / or skip the error. Suppose there is no primary key in the reference table and the record is duplicated. Applying selections with limit 1 will not catch this error.
2) Not in the pre-2011 SQL standard.
3) Not standardized in SQL92. Many DBMSs have analogues (top - MSSQL, Offset - like in Postgres; or, even worse - constructions with where rownum = in Oracle ). That is, changing the version of the DBMS, you may have to rewrite all such queries.
PS Such disputes about the expediency of using DBMS structures must be resolved from 2 points of view of the DBMS and the Application. We are sure that you have 99.999999% no errors in the implementation of DBMS objects and do not plan to switch to other DBMS - use limit, otherwise think about it.
PSS The dispute about the need for Order by is similar. Server people will say that order by is a prank, you should not load the server with the tasks of sorting the received data - this "peasant" business should fall on the shoulders of the application :)

Answer 2

practice - everything depends on the data. somewhere it is necessary to limit, somewhere it makes no sense.
how to hang - just request this data page many, many times

Answer 3

Without a very specific description, there is nothing to talk about.
You may or may not be limited.
There are also systems that do not allow you to go into memory overrun, even if the programmer is dumb.
But in general, yes, you need to limit. If you are not sure that you can not limit.

Answer 4

Be guided by common sense. When you have, for example, a selection by Primary Key, then you do not need LIMIT, because you will return one record, or if you select by occurrence, then you know the maximum number of records in advance.
If you are making a selection from a large amount of data (a table with 10 million records), then it is logical to do LIMIT to the amount that you need. And if you need to process everything, it's often better to process records in batches, requesting them 10/50/100/... shifting the list.
Strange question, in general.
People can't do that. Why repeat nonsense after them?

Answer 5

Strange, it seemed to me that wherever there is work with tables larger than a certain size (or those tables that can hypothetically become large, and these are almost all tables?), queries should be limited by limits,
tk. predicting queries that won't spit out the number of records that will be large for a script/client isn't very reliable.
Those. the limit can be provided with restrictions on the request, for example, the number of characters that can get into the request, for example, for an autocompletion script, BUT why all this magic if we can always limit the possible output with a simple LIMIT, without any consequences?
Those. in my opinion, LIMIT in a query is a thing of the same order as filtering input parameters.

Answer 6

I limit all queries, the number of results of which I know for sure.
For example, I submit products by filter for selection, where, among other things, there is a restriction on identifiers, such a cascade filter. From here I know that more than I submitted identifiers to the input in the sample will not only be less or equal, so I put down the limit. Single samples are mandatory with limit 1.