Going backwards in the pagination is easy: if we have a non-zero OFFSET, we know that we skipped rows to get to where we are which mean, we have rows to go back to. But, if we are going to provide tooling that allows the user to move from one page to another, how do we know if there is another page to link to? This is great because, for each page, we only have to read in and return the rows that we need to render. Consider a UI in which we show 10-records at a time to the user: This seems to violate the principle of least surprise which is why I always explicitly define both the LIMIT and OFFSET clause - no surprises!īy using the LIMIT and OFFSET clauses, we can start to approach paging through a larger result-set by thinking of each page as a combination of these clauses.

then the meaning of the first argument changes semantics. I don't care for this syntax because if you use LIMIT with only a single argument: If compatibility is not a concern, MySQL also supports a syntax wherein you pass two 'arguments' to the LIMIT clause as a comma-delimited list: That said, MySQL supports this syntax specifically for compatibility with PostgreSQL. I like to use both LIMIT and OFFSET together because it makes it super clear which value is which. In other words, this defines how many rows we skip ( N) before we start including rows in our result-set. OFFSET N - Used to determine the first row to be returned in the SELECT statement. MySQL can apply a number of query optimizations when this clause is present in the SQL. LIMIT R - Used to constrain the number of rows ( R) returned by the SELECT statement.

In MySQL, if we want to extract a subset of rows from a larger possible result-set, we can use LIMIT and OFFSET:

Mysql limit rows