In MySQL, the LIMIT clause is used to specify the number of records to return from a query result. It is often combined with the OFFSET keyword to skip a specific number of rows before starting to return rows. This is useful for pagination and retrieving a subset of results from a larger dataset.

Syntax

The syntax for LIMIT with OFFSET is:

SELECT column1, column2, ...
FROM table_name
LIMIT [offset,] row_count;

• offset: The number of rows to skip before starting to return rows. This is optional.
• row_count: The number of rows to return after the offset.

Alternatively, you can use:

SELECT column1, column2, ...
FROM table_name
LIMIT row_count OFFSET offset;

• row_count: The number of rows to return.
• offset: The number of rows to skip before starting to return rows.

Examples

1. Retrieve the First 10 Rows

   To get the first 10 rows from a table:

   SELECT *
   FROM employees
   LIMIT 10;
   

   This query retrieves the first 10 rows from the employees table.

2. Skip the First 5 Rows and Retrieve the Next 10 Rows

   To skip the first 5 rows and then retrieve the next 10 rows:

   SELECT *
   FROM employees
   LIMIT 10 OFFSET 5;
   

   This query skips the first 5 rows and retrieves the next 10 rows from the employees table.

3. Retrieve Rows Starting from a Specific Offset

   To use the alternative syntax to achieve the same result:

   SELECT *
   FROM employees
   LIMIT 5, 10;
   

   This query skips the first 5 rows and then retrieves the next 10 rows.

4. Pagination

   For implementing pagination in a web application, you can use LIMIT and OFFSET to return a specific page of results. For example, to get the results for page 3 with 20 rows per page:

   SELECT *
   FROM employees
   LIMIT 20 OFFSET 40;
   

   This query skips the first 40 rows (i.e., the results from pages 1 and 2) and retrieves the next 20 rows, which would be the results for page 3.

Best Practices

• Use with ORDER BY: When using LIMIT and OFFSET, it’s best to combine them with an ORDER BY clause to ensure a predictable and consistent ordering of the results.

  SELECT *
  FROM employees
  ORDER BY employee_id
  LIMIT 10 OFFSET 20;
  

  This query retrieves 10 rows starting from the 21st row, ordered by employee_id.

• Indexing: For large datasets, ensure that the columns used in ORDER BY are indexed to improve query performance, especially when using LIMIT and OFFSET with large offsets.

• Avoid High Offsets: Using high values for OFFSET can be inefficient, as MySQL needs to scan and discard many rows before returning the result. For large datasets, consider other pagination strategies such as keyset pagination.