How can I select rows in MySQL starting at a given row number?

How can I select rows in MySQL starting at a given row number?

  1. Blog
  2. 15 November 2026
  3. 3 min read

Stefan Bogdanescu

Founder & Senior Architect

Pagination Logic in MySQL: Skipping Rows with LIMIT

When building a data-heavy application, you quickly run into the mechanics of pagination. You have a dataset containing 50 rows, and your initial request fetches the first ten using LIMIT 10. The immediate challenge arises for subsequent pages: how do you retrieve the next set of rows without duplicating the ones already displayed?

The standard approach involves adjusting the offset within the LIMIT clause. If you want to start selection after the first ten rows, you modify the query parameters to skip those initial entries. In the context of a paginated gallery or user profile where images are ordered by ID, the logic shifts from simply fetching the first $N$ rows to fetching rows starting at index $M$.

Consider a scenario where you are fetching photos for a specific user. You have established an ordering preference, likely ORDER BY picid DESC to show the newest pictures first. To retrieve the next page of results, you must calculate a new starting point. The syntax remains consistent regardless of whether you are using raw SQL or an ORM like Laravel:

SELECT * 
FROM `picdb`
WHERE `username` = '$username'
ORDER BY `picid` DESC
LIMIT 10 OFFSET 10;

In this example, the database engine scans the result set from the beginning, discards the first 10 rows, and returns the subsequent 10. While some older tutorials might suggest passing an offset directly into the limit clause syntax (e.g., LIMIT '$start','$count'), modern MySQL practice separates these concerns for clarity. The $start variable represents the offset (number of rows to skip), and $count represents the number of rows to return.

// PHP example using raw query string interpolation
mysql_query("SELECT * FROM `picdb` WHERE `username` = '$username' ORDER BY `picid` DESC LIMIT $start, $count");

Note that this specific snippet uses the deprecated mysql_* extension. If you are maintaining legacy code or working in an environment where this is still supported, the logic holds: the first parameter after LIMIT dictates the starting row number (0-indexed), and the second parameter dictates how many rows to fetch. However, it is crucial to understand that $start is not a relative offset from the total count of rows satisfying the WHERE clause; it is an absolute skip count.

Performance Implications of Large Offsets

A common pitfall in pagination arises when dealing with large datasets and high page numbers. When you execute a query with LIMIT 10 OFFSET 1000, MySQL must still scan and evaluate the first 1,000 rows to identify them as "rows to skip" before it can return the requested 10. This behavior becomes a significant performance bottleneck as the offset grows.

The database engine does not have random access to the sorted result set unless an index exists on the columns used in the ORDER BY and WHERE clauses. If picid is indexed, MySQL can efficiently jump to the relevant rows. However, if your query relies solely on a composite condition like WHERE username = '$username' without an appropriate covering index, the optimizer might be forced to read from the table sequentially.

For high-performance applications, especially when handling thousands of rows, strategies often shift toward keyset pagination. Instead of using an offset, you fetch the picid of the last row returned in the previous page and use that ID as a starting point for the next query:

SELECT * 
FROM `picdb`
WHERE `username` = '$username' AND `picid` < $last_picid
ORDER BY `picid` DESC
LIMIT 10;

This approach eliminates the cost of skipping rows and ensures consistent performance regardless of how deep into the dataset you are navigating. While offset-based pagination is simpler to implement and easier for users to understand (e.g., "page 2"), keyset pagination is robust against data growth. Always ensure that any columns used in ORDER BY clauses within your pagination logic are properly indexed to maintain query efficiency.

Our network