The MySQL LIMIT and OFFSET Clauses [with Examples]

This tutorial covers limiting the number of results from a MySQL database query using the LIMIT clause and skipping results using the OFFSET clause.

This is especially useful when wanting to paginate results in web applications – spreading them out over several pages by retrieving a subset of the database results.

Examples

Examples will use the following table and data:

table_people

LIMIT

Here’s how to limit the results to 2 records using LIMIT:

SELECT * FROM table_people LIMIT 2;

Which will return:

| id | name |
|----|------|
|  1 |  Bob |
|  2 |  Ted |

OFFSET

You can also start the results at a specific position – skipping a set number of records before including records in the results.

This example skips the first three results and returns 2 results total:

SELECT * FROM table_people LIMIT 2 OFFSET 3;

Which returns:

| id | name |
|----|------|
|  4 |  Pip |
|  5 |  Zak |

The OFFSET clause cannot be used independently of the LIMIT clause.

Combined LIMIT and OFFSET

The above query using both LIMIT and OFFSET can also be written as:

SELECT * FROM table_people LIMIT 3, 2;

This removes the need to type out OFFSET by including it in the LIMIT clause as the first parameter.

I prefer not to combine them, as having them displayed with both the words LIMIT and OFFSET makes it clearer what’s going on when skimming over your code.

LIMIT, OFFSET & Pagination

The most common usage for LIMIT and OFFSET is providing pagination.

This is pretty simple and works in any programming language:

• LIMIT in this scenario is the number of records you want to display per page
• OFFSET is where the records on the page start at
  • So, if you are on page 3, OFFSET should be 3 * LIMIT.