PHP MySQL Limit Data

A table with a million rows returning all of them at once would overwhelm both the database and whatever is displaying the results -- LIMIT caps a query to return only a specific number of rows, the foundation of pagination (showing 20 results per page) and "top N" style queries (the 5 most recent orders).

In this page:

Basic LIMIT Usage
Pagination with LIMIT and OFFSET
Building a Full Pagination Script
Using LIMIT for Top-N Queries
Performance Considerations with LIMIT

Basic LIMIT Usage

Adding LIMIT n to the end of a SELECT statement caps the number of rows returned to at most n, regardless of how many rows actually matched the query -- SELECT * FROM products LIMIT 5 returns at most five products, even if the table holds thousands.

Note: Combine LIMIT with ORDER BY whenever the specific rows returned matter, since LIMIT alone offers no guarantee about which rows you get without an explicit sort.

Warning: LIMIT without ORDER BY can return a different set of rows on repeated identical queries, since the database is not obligated to return them in any consistent order.

Example: Basic LIMIT Usage

php

<?php
$db = new SQLite3(':memory:');
$db->exec("CREATE TABLE products (name TEXT)");
$db->exec("INSERT INTO products VALUES ('A'), ('B'), ('C'), ('D')");
$result = $db->query("SELECT * FROM products LIMIT 2");
while ($row = $result->fetchArray(SQLITE3_ASSOC)) {
    echo $row['name'] . "\n";
}
?>

Pagination with LIMIT and OFFSET

LIMIT n OFFSET m skips the first m matching rows, then returns up to n rows after that -- LIMIT 10 OFFSET 20 skips the first 20 rows and returns the next 10, which is exactly how page-based pagination is built: page 3 of a 10-per-page list uses OFFSET 20.

Note: Calculate the OFFSET as (pageNumber - 1) * pageSize, so page 1 correctly uses an offset of 0, not pageSize.

Warning: A very large OFFSET value on a large table can become noticeably slow, since the database still has to scan past every skipped row even though it discards them.

Example: Pagination with LIMIT and OFFSET

php

<?php
$db = new SQLite3(':memory:');
$db->exec("CREATE TABLE items (name TEXT)");
foreach (range(1, 30) as $i) { $db->exec("INSERT INTO items VALUES ('Item $i')"); }
$result = $db->query("SELECT * FROM items LIMIT 10 OFFSET 20");
while ($row = $result->fetchArray(SQLITE3_ASSOC)) {
    echo $row['name'] . "\n";
}
?>

Building a Full Pagination Script

A complete pagination setup needs two pieces of information beyond the page data itself: the total row count (via a separate COUNT query) to calculate the total number of pages, and the current page's slice of results using LIMIT/OFFSET -- together enabling "Page 3 of 12" style navigation controls.

Note: Run a lightweight COUNT(*) query separately from the main data query, rather than fetching every row just to count them in PHP.

Warning: Forgetting the total-count query means you cannot show total pages or disable a "next page" button once the last page has been reached.

Example: Building a Full Pagination Script

php

<?php
$db = new SQLite3(':memory:');
$db->exec("CREATE TABLE items (name TEXT)");
foreach (range(1, 25) as $i) { $db->exec("INSERT INTO items VALUES ('Item $i')"); }
$total = $db->querySingle("SELECT COUNT(*) FROM items");
$perPage = 10;
$totalPages = ceil($total / $perPage);
echo "Page 1 of $totalPages";
?>

Using LIMIT for Top-N Queries

Beyond pagination, LIMIT combined with ORDER BY is the standard way to answer "top N" questions -- the 3 highest-paid employees, the 10 best-selling products, the 5 most recent comments -- by sorting in the direction that puts the desired rows first, then limiting to just that count.

Note: For a "top N" query, sort in the direction that puts your desired rows first (DESC for highest/most, ASC for lowest/earliest), then apply LIMIT N.

Warning: Sorting in the wrong direction (ASC instead of DESC, or vice versa) for a "top N" query silently returns the bottom N instead, with no error to signal the mistake.

Example: Using LIMIT for Top-N Queries

php

<?php
$db = new SQLite3(':memory:');
$db->exec("CREATE TABLE employees (name TEXT, salary INTEGER)");
$db->exec("INSERT INTO employees VALUES ('Alice', 90000), ('Bob', 70000), ('Carol', 85000)");
$result = $db->query("SELECT * FROM employees ORDER BY salary DESC LIMIT 2");
while ($row = $result->fetchArray(SQLITE3_ASSOC)) {
    echo $row['name'] . "\n";
}
?>

Performance Considerations with LIMIT

LIMIT alone with no OFFSET is efficient since the database can stop scanning as soon as it has found enough matching rows -- but a large OFFSET forces it to still count past every skipped row first, which is worth keeping in mind for deep pagination (like page 500 of a huge table).

Note: For very deep pagination on large tables, consider "cursor-based" pagination (using a WHERE id > lastSeenId LIMIT n pattern) instead of a large OFFSET, which scales much better.

Warning: An index on the column used in ORDER BY makes both the sort and the LIMIT/OFFSET pagination noticeably faster on large tables -- an unindexed sort column can make deep pagination especially slow.

Example: Performance Considerations with LIMIT

php

<?php
$db = new SQLite3(':memory:');
$db->exec("CREATE TABLE items (name TEXT)");
foreach (range(1, 1000) as $i) { $db->exec("INSERT INTO items VALUES ('Item $i')"); }
$start = microtime(true);
$db->query("SELECT * FROM items LIMIT 10")->fetchArray();
echo "Small offset: " . round(microtime(true) - $start, 5) . "s";
?>

Common Mistakes

Fetching every row from the database and then slicing the results down to a page's worth in PHP, when LIMIT in the SQL itself is far more efficient and never transfers unneeded rows.
Forgetting to combine LIMIT with ORDER BY, since without an explicit sort, which rows LIMIT happens to return first is not guaranteed to be meaningful or consistent.
Calculating a pagination OFFSET incorrectly, like using the current page number directly instead of (page - 1) * pageSize, producing an off-by-one-page result.

Chapter Summary

LIMIT n restricts a query to return at most n rows.
LIMIT n OFFSET m skips the first m rows and then returns up to n rows after that, the basis of page-based pagination.
LIMIT should almost always be paired with ORDER BY, since the specific rows returned without an explicit sort are not guaranteed to be meaningful.

Browser Support

LIMIT (and OFFSET) are standard SQL and work identically across all MySQL and MariaDB versions PHP supports.

🔒

Chapter Quiz — Complete all 21 topics to unlock

0/21 topics done

Complete these topics first:

← Previous Lesson PHP MySQL Update Data Next Lesson → PHP MySQLi Connection