? MySQL LIMIT

Quick Overview

The LIMIT clause in MySQL restricts the number of rows returned by a query. It's commonly used for pagination, sampling results during testing, and improving performance when only a subset of data is needed.

Key Concepts

LIMIT — specifies the maximum number of rows to return.
OFFSET — skips a number of rows before returning results (often used with LIMIT for pagination).
Using LIMIT without ORDER BY can produce arbitrary rows; pair with ORDER BY for deterministic results.

Syntax / Theory

? View Code Example

-- Syntax for LIMIT: select desired columns and limit rows
SELECT column1, column2, ... FROM table_name LIMIT number_of_rows;

Code Example(s)

? View Code Example

-- Select the first 5 records from the 'students' table
SELECT * FROM students LIMIT 5;

? View Code Example

-- Select 10 records starting from the 11th record (skip first 10)
SELECT * FROM students LIMIT 10 OFFSET 10;

Live Output / Explanation

Explanation

LIMIT 5 returns only the first five rows from the result set.
LIMIT 10 OFFSET 10 skips the first ten rows and returns the next ten — useful for page 2 of a paginated set when page size = 10.
Always use ORDER BY when you need consistent ordering; otherwise row order is not guaranteed.

✅ Tips & Best Practices

Combine LIMIT with ORDER BY to paginate consistently.
Avoid large OFFSET values for deep pagination — consider keyset pagination for performance.
Use small limits while developing or sampling large tables to reduce resource use.

? Try It Yourself / Practice Tasks

Write a query to select the first 3 records from the students table.
Select records starting from the 6th row using OFFSET.
Use LIMIT with ORDER BY created_at DESC to fetch the latest N records deterministically.