Limit Offset

In production environments where tables may contain millions of rows, running a query that returns every record—such as:

SELECT * FROM products;

—is impractical. Retrieving millions of rows at once can overwhelm the database and client applications.

To handle large datasets efficiently, it's crucial to limit the number of rows returned in a query. This not only improves performance but also reduces the load on your infrastructure.

Using the LIMIT Keyword

The LIMIT keyword allows you to restrict your result set to a specified number of rows. For example, if you need to filter for specific products by their IDs, you might start with:

SELECT * FROM products WHERE id = 1 OR id = 2 OR id = 3;

To limit the results to only the first 10 rows, simply add the LIMIT keyword:

SELECT * FROM products LIMIT 10;

If a smaller subset is needed, such as the first 5 rows, adjust the query accordingly:

SELECT * FROM products LIMIT 5;

You can also apply LIMIT to more complex queries. For instance, if you want to select products priced above 10, and then limit the results:

SELECT * FROM products WHERE price > 10 LIMIT 2;

Ordering Results Before Limiting

You may need to order the results prior to applying a limit. For example, to sort products by their ID and then fetch the top five records:

SELECT * FROM products ORDER BY id LIMIT 5;

This query orders the dataset by product ID and returns the first five records from the sorted list.

Implementing OFFSET for Pagination

In many scenarios, particularly when implementing pagination, you might want to skip a certain number of rows. OFFSET comes into play in these cases. For example, to skip the first two rows in an ordered list:

SELECT * FROM products ORDER BY id LIMIT 5 OFFSET 2;

This instructs the database to bypass the initial two rows and then return the next five records. Adjusting the OFFSET value alters the starting position of the returned subset. For instance, to skip the first five rows:

SELECT * FROM products ORDER BY id LIMIT 5 OFFSET 5;

This combination of LIMIT and OFFSET is particularly useful for developing APIs that support pagination, as it provides precise control over the data subset returned in each query.