MySQL - Limit Clause

MySQL - The LIMIT Clause

The MySQL LIMIT Clause

The LIMIT clause is a powerful tool in MySQL used to constrain the number of rows returned by a SELECT query. It’s essential when dealing with large datasets, as fetching thousands or millions of records at once can be slow and consume significant memory. LIMIT allows you to retrieve a specific ‘slice’ or ‘page’ of data.

The clause takes one or two non-negative integer arguments: count and an optional offset.

Syntax

The basic syntax for the LIMIT clause has two common forms:

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

-- Or with an offset:
SELECT column1, column2, ...
FROM table_name
LIMIT offset, count;

-- Alternative (and clearer) syntax with OFFSET keyword (MySQL 8.0+):
SELECT column1, column2, ...
FROM table_name
LIMIT count OFFSET offset;

• count: Specifies the maximum number of rows to return.
• offset: Specifies the number of rows to skip before starting to return rows. The offset of the first row is 0, not 1. If the offset is omitted, it defaults to 0.

Initial Setup

Let’s set up a CUSTOMERS table to use in our examples. This table will store customer information.

-- Create the table
CREATE TABLE CUSTOMERS(
    ID INT NOT NULL AUTO_INCREMENT,
    NAME VARCHAR(50) NOT NULL,
    AGE INT NOT NULL,
    ADDRESS VARCHAR(100),
    SALARY DECIMAL(18, 2),
    PRIMARY KEY(ID)
);

-- Insert sample data
INSERT INTO CUSTOMERS(NAME, AGE, ADDRESS, SALARY) VALUES
('Ramesh', 32, 'Ahmedabad', 2000.00),
('Khilan', 25, 'Delhi', 1500.00),
('Kaushik', 23, 'Kota', 2000.00),
('Chaitali', 25, 'Mumbai', 6500.00),
('Hardik', 27, 'Bhopal', 8500.00),
('Komal', 22, 'Hyderabad', 4500.00),
('Muffy', 24, 'Indore', 10000.00);

If we query all records, our table looks like this:

SELECT * FROM CUSTOMERS;

ID	NAME	AGE	ADDRESS	SALARY
1	Ramesh	32	Ahmedabad	2000.00
2	Khilan	25	Delhi	1500.00
3	Kaushik	23	Kota	2000.00
4	Chaitali	25	Mumbai	6500.00
5	Hardik	27	Bhopal	8500.00
6	Komal	22	Hyderabad	4500.00
7	Muffy	24	Indore	10000.00

Example: Limiting the Number of Rows

To get only the first 4 customers from the table, we use LIMIT 4.

SELECT * FROM CUSTOMERS LIMIT 4;

Output

ID	NAME	AGE	ADDRESS	SALARY
1	Ramesh	32	Ahmedabad	2000.00
2	Khilan	25	Delhi	1500.00
3	Kaushik	23	Kota	2000.00
4	Chaitali	25	Mumbai	6500.00

Example: Limiting with an Offset

To implement pagination, we need to skip some rows. To get 4 customers starting from the 3rd row (which has an offset of 2), we use LIMIT 2, 4.

SELECT * FROM CUSTOMERS LIMIT 2, 4;

Output

The query skips the first two records (ID 1 and 2) and returns the next four.

ID	NAME	AGE	ADDRESS	SALARY
3	Kaushik	23	Kota	2000.00
4	Chaitali	25	Mumbai	6500.00
5	Hardik	27	Bhopal	8500.00
6	Komal	22	Hyderabad	4500.00

Practical Use Case: Pagination

Pagination is the most common use for LIMIT. Imagine you have a web page displaying customers, 10 per page. Here is how you would calculate the LIMIT and OFFSET for each page:

• Page 1: LIMIT 10 OFFSET 0 (or LIMIT 0, 10)
• Page 2: LIMIT 10 OFFSET 10 (or LIMIT 10, 10)
• Page 3: LIMIT 10 OFFSET 20 (or LIMIT 20, 10)
• General Formula: offset = (page_number - 1) * items_per_page, count = items_per_page

Using LIMIT with WHERE Clause

You can combine LIMIT with a WHERE clause to restrict rows from a filtered result set. The WHERE clause is applied first, and then LIMIT is applied to the result of that filter.

Example

Let’s find the first two customers who are older than 25.

SELECT * FROM CUSTOMERS WHERE AGE > 25 LIMIT 2;

Output

ID	NAME	AGE	ADDRESS	SALARY
1	Ramesh	32	Ahmedabad	2000.00
5	Hardik	27	Bhopal	8500.00

Using LIMIT with ORDER BY Clause

Using LIMIT without ORDER BY can produce unpredictable results, as the database does not guarantee a specific row order unless explicitly told. For consistent results, especially for features like ‘Top 5 Earners’ or ‘10 Most Recent Posts’, you must use ORDER BY.

Example: Finding the Top 3 Earners

To find the customers with the top 3 highest salaries, we sort by SALARY in descending order and then take the top 3.

SELECT * FROM CUSTOMERS
ORDER BY SALARY DESC
LIMIT 3;

Output

ID	NAME	AGE	ADDRESS	SALARY
7	Muffy	24	Indore	10000.00
5	Hardik	27	Bhopal	8500.00
4	Chaitali	25	Mumbai	6500.00

Performance Considerations

While LIMIT is essential for performance, it has a major pitfall with large offsets. A query like LIMIT 1000000, 10 is very inefficient. The database must still generate and scan all 1,000,010 rows before it can discard the first million and return the final 10. This is known as large offset pagination.

Pro-Tip: For applications with ‘infinite scroll’ or deep pagination, consider ‘keyset pagination’ (or the ‘seek method’). Instead of an offset, you use a WHERE clause based on the last value you saw. For example: WHERE id > [last_seen_id] ORDER BY id ASC LIMIT 10. This is much faster as it can use an index to jump directly to the starting point.

Using LIMIT in Application Code

In real-world applications, you’ll execute these queries from your backend code. It is critical to use parameterized queries (prepared statements) to prevent SQL injection vulnerabilities. Never format user input directly into a SQL string.

Setup Note

Before running the following examples, ensure you have the appropriate database driver installed for your language:

• PHP: The mysqli extension is usually enabled by default.
• Node.js: npm install mysql2
• Java: Add the MySQL Connector/J dependency via Maven or Gradle.
• Python: pip install mysql-connector-python

PHP
NodeJS
Java
Python



// Assumes $dbhost, $dbuser, $dbpass, $dbname are defined
$mysqli = new mysqli($dbhost, $dbuser, $dbpass, $dbname);
if ($mysqli->connect_errno) {
    // In production, log this error, don't show to the user
    die("Connection failed: " . $mysqli->connect_error);
}

$limit = 4;
$offset = 2;

// Use prepared statements to prevent SQL injection
$stmt = $mysqli->prepare("SELECT ID, NAME, AGE, SALARY FROM CUSTOMERS ORDER BY ID LIMIT ?, ?");

// 'ii' means two integer parameters
$stmt->bind_param('ii', $offset, $limit);

$stmt->execute();
$result = $stmt->get_result();

$customers = $result->fetch_all(MYSQLI_ASSOC);

print_r($customers);

$stmt->close();
$mysqli->close();



// Use modern async/await syntax with mysql2/promise
const mysql = require('mysql2/promise');

async function getCustomers() {
  let connection;
  try {
    connection = await mysql.createConnection({
      host: 'localhost',
      user: 'root',
      password: 'password',
      database: 'TUTORIALS'
    });

    const limit = 4;
    const offset = 2;

    const sql = "SELECT ID, NAME, AGE, SALARY FROM CUSTOMERS ORDER BY ID LIMIT ?, ?";
    const [rows] = await connection.execute(sql, [offset, limit]);

    console.log('Fetched customers:');
    console.log(rows);

  } catch (error) {
    console.error('Database query failed:', error);
  } finally {
    if (connection) {
      await connection.end();
    }
  }
}

getCustomers();



import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class LimitQueryExample {
    // JDBC driver name and database URL
    static final String JDBC_DRIVER = "com.mysql.cj.jdbc.Driver";
    static final String DB_URL = "jdbc:mysql://localhost:3306/TUTORIALS";

    // Database credentials
    static final String USER = "root";
    static final String PASS = "password";

    public static void main(String[] args) {
        String sql = "SELECT ID, NAME, AGE, SALARY FROM CUSTOMERS ORDER BY ID LIMIT ?, ?";
        int limit = 4;
        int offset = 2;

        // Use try-with-resources for automatic resource management
        try (Connection conn = DriverManager.getConnection(DB_URL, USER, PASS);
             PreparedStatement pstmt = conn.prepareStatement(sql)) {

            pstmt.setInt(1, offset);
            pstmt.setInt(2, limit);

            try (ResultSet rs = pstmt.executeQuery()) {
                while (rs.next()) {
                    System.out.printf("ID: %d, Name: %s, Age: %d, Salary: %.2f\n",
                            rs.getInt("ID"),
                            rs.getString("NAME"),
                            rs.getInt("AGE"),
                            rs.getBigDecimal("SALARY"));
                }
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}



import mysql.connector
from mysql.connector import errorcode

def get_paginated_customers():
    config = {
        'user': 'root',
        'password': 'password',
        'host': 'localhost',
        'database': 'TUTORIALS'
    }

    limit = 4
    offset = 2
    query = "SELECT ID, NAME, AGE, SALARY FROM CUSTOMERS ORDER BY ID LIMIT %s, %s"

    try:
        # Use 'with' statements for proper resource management
        with mysql.connector.connect(**config) as connection:
            with connection.cursor(dictionary=True) as cursor:
                cursor.execute(query, (offset, limit))
                customers = cursor.fetchall()

                print("Fetched customers:")
                for customer in customers:
                    print(customer)

    except mysql.connector.Error as err:
        if err.errno == errorcode.ER_ACCESS_DENIED_ERROR:
            print("Something is wrong with your user name or password")
        elif err.errno == errorcode.ER_BAD_DB_ERROR:
            print("Database does not exist")
        else:
            print(err)

if __name__ == "__main__":
    get_paginated_customers()