MySQL LIMIT & OFFSET s primjerima

What is the LIMIT Keyword in MySQL?

The OGRANIČAVA keyword restricts the number of rows returned in a query result. It can be used with the SELECT, UPDATE, and DELETE statements, so it caps the rows a query reads as well as the rows a write affects.

The syntax for the LIMIT keyword is as follows.

SELECT {fieldname(s) | *} FROM tableName(s) [WHERE condition] LIMIT N;

OVDJE

• “SELECT {fieldname(s) | *} FROM tableName(s)” je SELECT izraz koji sadrži polja koja želimo vratiti u našem upitu.
• “[WHERE uvjet]” is optional, but when supplied it specifies a filter on the result set. The WHERE klauzula is applied before LIMIT, so filtering happens first and the cap is applied to what survives.
• “LIMIT N” is the keyword, and N is any number starting from 0. Putting 0 as the limit returns no records at all. Putting a number such as 5 returns five records. If the table holds fewer records than N, all of them are returned and no error is raised.

The syntax is short, but the reason it exists is worth stating before the examples.

Why Should We Use the LIMIT Keyword?

Pretpostavimo da se razvijamoping the application that runs on top of myflixdb. The system designers have asked us to limit the number of records displayed on a page to 20 records, in order to counter slow load times. How do we implement a system that meets such a requirement?

The LIMIT keyword handles exactly this situation. Instead of pulling every member row into the application and discarding most of them, the query returns 20 records per page and the database does the work. Three benefits follow from that.

• Brži odgovor: less data is read from disk and less data crosses the network.
• Lower memory use: the application holds one page of rows, not the whole table.
• Safer writes: a LIMIT on an UPDATE or a DELETE statement caps how many rows a mistake can touch.

MySQL LIMIT Query Examples

The examples below run against the members table of the myflixdb database. The first one returns two rows and nothing more.

SELECT * FROM members LIMIT 2;

As the result above shows, only two members have been returned.

Getting a list of ten (10) members from the database

Suppose we want a list of the first 10 registered members from the Myflix database. The script below asks for them.

SELECT * FROM members LIMIT 10;

Executing the script gives the result shown below.

Only 9 members have been returned, because N in the LIMIT clause is greater than the number of records in the table. Asking for 9 rows explicitly produces the same result set.

SELECT * FROM members LIMIT 9;

💡 Savjet: LIMIT selects rows from whatever order the server happens to produce. Add an NARUČITE PO clause whenever the identity of the rows matters, otherwise “the first 10 members” is not guaranteed to mean the same nine people twice.

Limiting the row count is the first half of the feature. Choosing where the window starts is the second.

Using the OFFSET Value in the LIMIT Query

The OFFSET value is most often used together with the LIMIT keyword. It specifies which row the server starts retrieving data from, so rows before that point are skipped.

Suppose we want a limited number of members starting from the middle of the table. The script below starts at the second row and limits the result to two records.

SELECT * FROM `members` LIMIT 1, 2;

Executing it in MySQL Radna tezga against the myflixdb gives the following result.

Note that here OFFSET = 1, hence row #2 is the first row returned, and LIMIT = 2, hence only 2 records come back.

In the two-argument form the offset is written first and the row count second, which is easy to reverse by accident. MySQL also accepts an explicit form that removes the ambiguity, and it is the one to prefer in new code.

SELECT * FROM `members` LIMIT 2 OFFSET 1;

Both statements return the same two rows. With the offset understood, the pagination pattern that every listing screen relies on falls out of it directly.

How to Paginate Query Results with LIMIT and OFFSET

Pagination splits a large result set into numbered pages, and LIMIT together with OFFSET is the mechanism that does it. Two values drive every page request: the page size, which is how many records appear on one screen, and the page number requested by the user.

The offset is derived from them with a single formula.

-- OFFSET = page_size * (page_number - 1)
SELECT membership_number, full_names
FROM members
ORDER BY membership_number ASC
LIMIT 20 OFFSET 0;   -- page 1

Page 2 keeps the same limit and moves the offset forward by one page size.

SELECT membership_number, full_names
FROM members
ORDER BY membership_number ASC
LIMIT 20 OFFSET 20;  -- page 2

Three rules keep a paginated listing correct and quick.

1. Always sort: a paginated query without ORDER BY can show the same record on two different pages and hide another one completely, because the server is free to change the row order between calls.
2. Sort on a unique column: ties in the sort column leave the order of the tied rows undefined. Sorting on the primary key, or adding it as a tie-breaker, removes the problem.
3. Watch deep pages: OFFSET 100000 forces MySQL to read one hundred thousand rows and throw them away before returning the next twenty. Response time grows with the page number.

For very deep pagination, keyset pagination avoids the offset entirely. Instead of counting rows to skip, the query remembers the last key from the previous page and asks for the rows after it.

SELECT membership_number, full_names
FROM members
WHERE membership_number > 20      -- last id from the previous page
ORDER BY membership_number ASC
LIMIT 20;

This form stays fast at any depth, because the index jumps straight to the starting key rather than walking the rows in front of it. The trade-off is that pages must be walked in sequence, so jumping directly to page 500 is no longer possible.

LIMIT u MySQL vs TOP and FETCH FIRST

LIMIT is not part of every SQL dialect, which matters as soon as a query has to move between database engines. MySQL, PostgreSQLi SQLite share the LIMIT keyword. SQL Server uses TOP, and Oracle uses the standard FETCH FIRST clause. The table below compares the three.

The behaviour is the same in each case: cap the number of rows and, optionally, skip a number of rows first. Only the spelling changes. A query that must run on more than one engine should therefore isolate the row-limiting clause rather than scatter it through the codebase.

Pitanja i odgovori