MySQL: LIMIT Clause

By Atul Rai | Last Updated: August 2, 2023 Previous       Next

MySQL LIMIT is a clause used in SQL queries to restrict the number of rows returned from a database table. It allows you to define the maximum number of records to be retrieved, starting from the first row of the result set. By using this clause, you can prevent fetching excessive data and significantly improve query performance.

Syntax

SELECT column1, column2, ...
FROM table_name
LIMIT number_of_rows;

In this syntax:

• column1, column2, ...: Specifies the columns you want to retrieve data from.
• table_name: Refers to the table containing the data.
• number_of_rows: Represents the maximum number of rows to fetch.

How to use MySQL LIMIT?

To better understand MySQL LIMIT, let’s explore some practical scenarios:

1. Fetching a Specific Number of Rows

Suppose we have a table named “employees” containing various employee records. We want to retrieve the first five employees from the table. We can use the LIMIT clause as follows:

SELECT 
  * 
FROM
  employees 
LIMIT 5 ;

This query will return the first five rows from the “employees” table.

2. Pagination Support

LIMIT is particularly useful for implementing pagination in web applications. Instead of retrieving all records at once, which can be resource-intensive, you can fetch data in small chunks. For example, to fetch records for page 2, each page displaying 10 records:

SELECT 
  * 
FROM
  employees 
LIMIT 10 OFFSET 10 ;

In this query, OFFSET 10 skips the first 10 rows, and LIMIT 10 retrieves the next ten rows, giving us records 11 to 20.

3. Combining with ORDER BY

The LIMIT clause works harmoniously with the ORDER BY clause to retrieve specific rows based on a particular order. For example, if we want the five highest-paid employees from the “employees” table:

SELECT 
  * 
FROM
  employees 
ORDER BY salary DESC 
LIMIT 5 ;

This query will first sort the data in descending order of salary and then retrieve the first five rows, representing the top earners.

References

1. LIMIT Query Optimization – MySQL Doc