MySQL Limit Data

MySQL Limit Data Returning Only Some Rows In simple terms: A product table might contain thousands of rows, but a homepage shows the "Top 3 best sellers", a search page shows 20 results at a time, and an existence check needs just one row. LIMIT tells the database to return at most a certain number of rows — fewer rows to send, process, and display.

Technically: LIMIT count restricts the result to the first count rows; LIMIT count OFFSET skip (or LIMIT skip, count) skips rows first. LIMIT is applied after WHERE and ORDER BY, so it must be combined with ORDER BY to return meaningful, predictable rows. It also works with UPDATE and DELETE.

The examples use the products from MySQL Insert Multiple (after MySQL Update Data, the Desk Lamp has 6 in stock).

Syntax SELECT ... FROM ... WHERE ... ORDER BY ... LIMIT count; SELECT ... FROM ... WHERE ... ORDER BY ... LIMIT count OFFSET skip; SELECT ... FROM ... WHERE ... ORDER BY ... LIMIT skip, count; -- same as above, older form Example Returns LIMIT 3 The first 3 rows LIMIT 2 OFFSET 2 Rows 3 and 4 (skip 2, take 2) LIMIT 2, 2 Same as LIMIT 2 OFFSET 2 (note the reversed order: skip first) Top-N Queries <?php require DIR . '/src/db.php';

$db = db();

echo "Most expensive:\n"; foreach ($db->query('SELECT name, price FROM products ORDER BY price DESC LIMIT 3') as $row) { echo " {$row['name']} (₹{$row['price']})\n"; }

echo "Cheapest:\n"; foreach ($db->query('SELECT name, price FROM products ORDER BY price ASC LIMIT 2') as $row) { echo " {$row['name']} (₹{$row['price']})\n"; } Output Most expensive: Desk Lamp (₹1299.00) Backpack (₹899.00) Wireless Mouse (₹599.00) Cheapest: Gel Pen (₹20.00) Notebook A5 (₹60.00) How It Works ORDER BY price DESC sorts all products from most to least expensive. LIMIT 3 keeps only the first three rows of that sorted result. Changing only the direction and the limit gives the cheapest products. Without ORDER BY, LIMIT 3 returns "some three rows" — which three is not defined and may change. Always sort before limiting.

Skipping Rows With OFFSET <?php require DIR . '/src/db.php';

$stmt = db()->prepare('SELECT id, name FROM products ORDER BY id LIMIT ? OFFSET ?');

$perPage = 2; $offset = 2; // skip the first page $stmt->bind_param('ii', $perPage, $offset); $stmt->execute();

foreach ($stmt->get_result() as $row) { echo "#{$row['id']} {$row['name']}\n"; } Output #3 Desk Lamp #4 Backpack OFFSET 2 skips the first two rows (IDs 1 and 2); LIMIT 2 then takes the next two. When LIMIT and OFFSET values come from the request (for example, a page number), pass them as integer parameters with bind_param('ii', ...). Values sent as strings, as with execute([...]) or execute_query(), are not accepted reliably for LIMIT. This is the basis of page-by-page navigation, covered fully in Pagination with PHP.

LIMIT 1 for Single Rows and Existence Checks When you need at most one row, LIMIT 1 lets MySQL stop searching as soon as it finds a match:

<?php require DIR . '/src/db.php';

function emailExists(mysqli $db, string $email): bool { return $db->execute_query('SELECT 1 FROM customers WHERE email = ? LIMIT 1', [$email])->num_rows === 1; }

var_dump(emailExists(db(), 'asha@example.com')); var_dump(emailExists(db(), 'nobody@example.com')); Output bool(true) bool(false) SELECT 1 returns a constant instead of real columns, because only the existence of a row matters. (For inserts, still rely on the UNIQUE constraint rather than a check-then-insert sequence — see MySQL Insert Data.)

Top-N per Group (MySQL 8 Window Functions) LIMIT applies to the whole result. To get, for example, the most expensive product in each category, use the window function ROW_NUMBER() (MySQL 8.0+):

<?php require DIR . '/src/db.php';

$sql = ' SELECT category, name, price FROM ( SELECT category, name, price, ROW_NUMBER() OVER (PARTITION BY category ORDER BY price DESC) AS position FROM products ) AS ranked WHERE position = 1 ORDER BY category ';

foreach (db()->query($sql) as $row) { printf("%-12s %-12s ₹%s\n", $row['category'], $row['name'], $row['price']); } Output bags Backpack ₹899.00 electronics Desk Lamp ₹1299.00 stationery Notebook A5 ₹60.00 ROW_NUMBER() numbers the rows within each category (PARTITION BY category) by descending price. Keeping only position 1 gives the top product per category. Change the condition to position <= 3 for the top three per category.

LIMIT With UPDATE and DELETE LIMIT restricts how many rows a modifying statement may affect:

-- Process the oldest 100 pending notifications UPDATE notifications SET status = 'processing' WHERE status = 'pending' ORDER BY created_at LIMIT 100;

-- Delete old log entries in manageable chunks DELETE FROM activity_log WHERE created_at < '2025-01-01' LIMIT 1000; This is useful for batch jobs: each run handles a bounded amount of work, keeping locks short. (LIMIT with UPDATE/DELETE supports a row count only — no OFFSET.)

Performance: Large Offsets LIMIT 20 OFFSET 100000 does not jump directly to row 100,001: MySQL must find and discard the first 100,000 rows. For deep pages in large tables, this becomes slow. Keyset pagination ("seek method") avoids the problem by continuing after the last seen value instead of counting rows:

SELECT id, name FROM products WHERE id > ? ORDER BY id LIMIT 20; Both techniques are compared in Pagination with PHP.

Common Mistakes LIMIT Without ORDER BY Effect: Unpredictable rows; pages may overlap or skip rows.

Fix: Always sort, with a unique tie-breaker column.

Mixing Up LIMIT skip, count Effect: LIMIT 20, 10 returns 10 rows after skipping 20 — not 20 rows.

Fix: Prefer the clearer LIMIT count OFFSET skip form.

Passing Limits as Strings or Unvalidated Input Effect: Errors, or huge result sets when users request limit=1000000.

Fix: Validate as integers, enforce a maximum, and bind with type i.

Fetching Everything and Slicing in PHP Effect: Wasted memory and time.

Fix: Let the database limit the result.

Best Practices Combine LIMIT with a deterministic ORDER BY. Validate limit and offset values, cap them, and bind them as integers. Use LIMIT 1 for single-row lookups and existence checks. Use window functions for top-N-per-group results. Prefer keyset pagination for deep pages in large tables. Official reference: https://www.php.net/manual/en/mysqli.query.php