MySQL LIMIT and OFFSET Explained with Simple Examples

Originally published on CodingCush.

In MySQL, LIMIT controls how many rows a query returns, and OFFSET controls how many rows to skip before returning them. Together they answer questions like "show me the first 10 students" or "show me page 3 of the results, 10 per page". If you have ever seen a website split a long list into pages, this simple pair of clauses is almost certainly doing the work behind the scenes.

Why Limit Rows at All?

A table can hold ten rows today and ten lakh rows next year. A query with no limit happily returns all of them — slowly, and in no useful order for a human reader. Limiting rows keeps queries fast, keeps pages light, and gives users results in digestible chunks. It is the database version of asking a librarian for the first shelf of books instead of having the whole library dumped on your desk. MySQL itself rewards this habit: the official manual explains that when you need only a specified number of rows, using a LIMIT clause is better than fetching the whole result and throwing the extra data away. The MySQL Reference Manual page on LIMIT query optimisation goes into how MySQL can even stop early once it has found enough rows.

LIMIT: The Basic Form

Start with a simple students table. To fetch only the first five rows, add LIMIT 5 at the end of your query:

SELECT id, name, marks FROM students LIMIT 5; 

That returns at most five rows — fewer if the table is smaller, which is perfectly normal and not an error. One warning that surprises every beginner sooner or later: without an ORDER BY, "first five" means "any five the database happens to reach first". The order is not promised, and it can change. If order matters — and for humans it almost always does — always sort first, as the next section shows.

ORDER BY + LIMIT: Top 10 Lists

The most famous use of LIMIT is the top-N list: the ten highest marks, the five newest posts, the three cheapest products. The pattern is always the same — sort by the column you care about, then limit.

SELECT id, name, marks FROM students ORDER BY marks DESC LIMIT 10; 

Here DESC sorts from highest to lowest, so the limit keeps the top ten scorers. Flip it to ASC and you get the ten lowest instead. The full syntax of the statement, including every clause order, is documented on the official MySQL SELECT syntax page — a dry page, admittedly, but the final word when a query behaves strangely.

The official MySQL 8.4 Reference Manual page on LIMIT query optimisation on dev.mysql.com.

OFFSET: Skipping Rows for Pagination

OFFSET skips rows before the limit applies. LIMIT 10 OFFSET 10 skips the first ten rows and returns the next ten — in other words, page two, if each page shows ten rows. The maths for any page is simple:

The formula is: offset = (page − 1) × rows per page. MySQL also offers a shorter two-number form, LIMIT 20, 10, which means "skip 20, take 10" — the same as LIMIT 10 OFFSET 20. Both forms are correct; the long form with the OFFSET keyword is easier for beginners to read six months later, which is a perfectly good reason to prefer it.

-- Page 3, ten rows per page SELECT id, name, marks FROM students ORDER BY id LIMIT 10 OFFSET 20; 

Notice the ORDER BY id. For pagination it is not optional decoration: pages must be slices of one stable order, otherwise the same row can appear on two pages, or vanish between them, like a book whose pages shuffle themselves while you read.

Using LIMIT in a Real PHP Page

In a real website, the page number arrives from the URL, and PHP turns it into an offset. That is exactly the technique behind our tutorial on adding search and pagination to a PHP and MySQL listing page, which builds the full feature with page links. The core idea fits in a few lines:

$page = max(1, (int)($_GET['page'] ?? 1)); $perPage = 10; $offset = ($page - 1) * $perPage; $stmt = $pdo->prepare( "SELECT id, name, marks FROM students ORDER BY id LIMIT :limit OFFSET :offset" ); $stmt->bindValue(':limit', $perPage, PDO::PARAM_INT); $stmt->bindValue(':offset', $offset, PDO::PARAM_INT); $stmt->execute(); 

Two details matter here. First, cast the page number to an integer — never trust URL input as-is. Second, bind the limit and offset as integers, not strings; MySQL is picky about that, and many a beginner has lost an evening to this exact quirk. If the language-versus-software distinction still feels fuzzy, our explainer on SQL vs MySQL clears it up in a few minutes.

One More Everyday Pattern: The Latest Five

Blogs, shops and dashboards all share one favourite query: "show the latest five". Latest means sorted by date or by ID, highest first, limited to five — the same top-N pattern, just wearing a different shirt.

SELECT id, title, created_at FROM posts ORDER BY created_at DESC LIMIT 5; 

Try it with different numbers and orders until the pattern feels automatic: newest five posts, oldest three comments awaiting moderation, the two most recent orders for one customer (add a WHERE customer_id = 7 before the ORDER BY). Notice how WHERE filters first, ORDER BY sorts what remains, and LIMIT trims the sorted result last. That clause order — filter, sort, trim — is the mental model that makes almost every reporting query fall into place. Once it clicks, you will find yourself reading other people's queries and understanding them at a glance, which is a quietly satisfying milestone on the road from beginner to working developer.

Common Mistakes to Avoid

First, forgetting ORDER BY, as covered above — the classic. Second, off-by-one page maths: page 1 must use offset 0, not offset 10, or your first ten rows will never be seen by anyone. Third, huge offsets on very large tables: OFFSET 100000 still makes MySQL walk past those rows, so deep pages get slower. For ordinary student and small-business projects this is fine, but if you ever build something huge, look up keyset pagination, which jumps by the last seen ID instead. Fourth, limiting without thinking about totals: a page footer that says "Page 2 of 9" needs a separate count query, because LIMIT deliberately does not tell you how many rows exist in total.

What is the difference between LIMIT and OFFSET?

LIMIT says how many rows to return. OFFSET says how many rows to skip first. Page 1 uses an offset of 0; every later page increases it.

Does LIMIT make my query faster?

Often, yes. MySQL can stop once it has enough rows, especially with a matching index and ORDER BY. But a very large OFFSET still costs time, because the skipped rows must be counted past.

What does LIMIT 5, 10 mean?

It is the short form: skip 5 rows, then return 10. That is the same as LIMIT 10 OFFSET 5. The first number is the offset, the second is the count.

Can I use LIMIT with UPDATE or DELETE?

Yes, MySQL allows a simple LIMIT on those statements too, for example deleting only the oldest 100 rows at a time. There is no OFFSET in that form, so use it carefully and always with a clear ORDER BY.

Conclusion

LIMIT decides how many rows come back, OFFSET decides where the window starts, and ORDER BY keeps the whole thing honest. Memorise the page formula — offset equals page minus one, times rows per page — and you can build top-10 lists, paginated tables and tidy reports in any project. It is a small clause with an outsized effect on how professional your application feels.