Python MySQL Limit

A LIMIT clause caps how many rows a SELECT statement returns -- essential for pagination, previewing a large table, or simply fetching just the top few results (often combined with ORDER BY to get, say, the 5 most recent or highest-value rows).

In this page:

Basic LIMIT Usage
Getting the "Top N" Results
Skipping Rows with OFFSET
Building a Reusable Pagination Function
LIMIT Without OFFSET vs a Full Fetch

Basic LIMIT Usage

cursor.execute("SELECT * FROM customers LIMIT 5") returns at most the first 5 rows the query would otherwise return, regardless of how many total rows actually match -- useful for quickly previewing a large table without pulling every row.

Note: Add LIMIT whenever previewing or sampling a large table during development, to avoid accidentally pulling thousands of rows into memory or the terminal.

Warning: Without an accompanying ORDER BY, which specific rows LIMIT returns is not guaranteed to be meaningful or consistent between runs.

Example: Basic LIMIT Usage

python

from unittest.mock import MagicMock

conn = MagicMock()
cursor = conn.cursor()
cursor.execute("SELECT * FROM customers LIMIT 5")
print("Returns at most 5 rows")

Getting the "Top N" Results

Combining ORDER BY ... DESC with LIMIT n is the standard pattern for retrieving the top N results by some measure -- for example, the 5 most expensive products, or the 10 most recent orders.

Note: Always pair LIMIT with an explicit ORDER BY when the goal is genuinely "the top N by some criteria" -- LIMIT alone does not guarantee any particular ordering.

Warning: Ties at the boundary of the LIMIT (multiple rows with the identical sort value at the cutoff point) can make which specific row appears last somewhat arbitrary unless a tie-breaking column is added to ORDER BY.

Example: Getting the "Top N" Results

python

from unittest.mock import MagicMock

conn = MagicMock()
cursor = conn.cursor()
cursor.execute("SELECT * FROM products ORDER BY price DESC LIMIT 5")
print("The 5 most expensive products")

Skipping Rows with OFFSET

LIMIT n OFFSET m skips the first m matching rows entirely, then returns up to n rows starting after that point -- the fundamental mechanism behind paginated results, like "page 2" of a search results list.

Note: Calculate OFFSET as (page_number - 1) * page_size when implementing pagination, a common formula worth memorizing.

Warning: A very large OFFSET value on a huge table can be noticeably slow, since MySQL still internally processes and discards every skipped row before reaching the ones actually returned.

Example: Skipping Rows with OFFSET

python

from unittest.mock import MagicMock

conn = MagicMock()
cursor = conn.cursor()
cursor.execute("SELECT * FROM customers ORDER BY id LIMIT 10 OFFSET 10")
print("Page 2 of results, 10 per page")

Building a Reusable Pagination Function

Wrapping the LIMIT/OFFSET pattern in a small Python function that accepts a page number and page size makes pagination logic reusable and consistent across every place in an application that needs to page through results.

Note: Centralize pagination logic in one reusable function rather than recalculating LIMIT and OFFSET by hand at every call site, reducing the chance of an off-by-one page-size bug.

Warning: Always validate that page_number is at least 1 before computing OFFSET -- a page_number of 0 or negative produces a nonsensical or invalid negative OFFSET.

Example: Building a Reusable Pagination Function

python

def paginate(page_number, page_size=10):
    offset = (page_number - 1) * page_size
    return f"LIMIT {page_size} OFFSET {offset}"

print(paginate(2))

LIMIT Without OFFSET vs a Full Fetch

Applying LIMIT at the SQL level (letting the database itself return only the needed rows) is far more efficient than fetching an entire table with fetchall() and then slicing the resulting Python list down to size, since the unwanted rows never even cross the network in the first place.

Note: Always push row-limiting logic into the SQL query itself with LIMIT, rather than fetching everything and slicing the result in Python afterward.

Warning: result = cursor.fetchall()[:5] still transfers and holds every single row in memory before discarding all but 5 -- a real, avoidable performance cost on a large table.

Example: LIMIT Without OFFSET vs a Full Fetch

python

from unittest.mock import MagicMock

conn = MagicMock()
cursor = conn.cursor()
cursor.execute("SELECT * FROM customers LIMIT 5")  # efficient: filtered in SQL
print("Only 5 rows ever cross the network")

Common Mistakes

Assuming LIMIT alone gives a meaningful 'top N' result without an accompanying ORDER BY -- without sorting first, LIMIT simply returns an arbitrary N rows, not necessarily the ones intended.
Miscalculating the OFFSET value when implementing pagination, causing rows to be skipped entirely or shown twice across pages.
Using LIMIT with a very large OFFSET on a huge table, which can be surprisingly slow since MySQL still has to scan past all the skipped rows internally.

Chapter Summary

LIMIT n restricts a SELECT statement to returning at most n rows.
LIMIT n OFFSET m skips the first m rows, then returns up to n rows after that, enabling pagination.
LIMIT is almost always combined with ORDER BY to get a meaningful "top N" or "most recent N" result.

Browser Support

LIMIT (and OFFSET) syntax is standard MySQL, supported identically across all mysql-connector-python versions.

🔒

Chapter Quiz — Complete all 12 topics to unlock

0/12 topics done

Complete these topics first:

← Previous Lesson Python MySQL Update Table Next Lesson → Python MySQL Join

Python MySQL Limit

Basic LIMIT Usage

Getting the "Top N" Results

Skipping Rows with OFFSET

Building a Reusable Pagination Function

LIMIT Without OFFSET vs a Full Fetch

Chapter Quiz — Complete all 12 topics to unlock

Login to run this code