How to show N entries in result of a select SQL query to MySQL?

ByDavid Yang

Using LIMIT to Restrict Query Results

The LIMIT clause in MySQL lets you control how many rows a SELECT query returns. This is essential for pagination, performance tuning, and working with large datasets.

Basic LIMIT Syntax

To return only the first N rows from a table:

SELECT * FROM qa_users LIMIT 10;

This returns the first 10 rows from the qa_users table. Simple and straightforward.

LIMIT with OFFSET

When you need to skip rows (for pagination), combine LIMIT with OFFSET:

SELECT * FROM qa_users LIMIT 10 OFFSET 20;

This skips the first 20 rows and returns the next 10 rows (rows 21-30). This is commonly written as:

SELECT * FROM qa_users LIMIT 20, 10;

Both syntaxes are equivalent — LIMIT offset, count is just older notation. The explicit OFFSET keyword is clearer and recommended.

Practical Example: Pagination

If you’re building a paginated API returning 50 results per page:

-- Page 1 (rows 1-50)
SELECT id, username, email FROM qa_users LIMIT 50 OFFSET 0;

-- Page 2 (rows 51-100)
SELECT id, username, email FROM qa_users LIMIT 50 OFFSET 50;

-- Page 3 (rows 101-150)
SELECT id, username, email FROM qa_users LIMIT 50 OFFSET 100;

With filtering and ordering:

SELECT id, username, email, created_at 
FROM qa_users 
WHERE status = 'active' 
ORDER BY created_at DESC 
LIMIT 25 OFFSET 0;

Performance Considerations

  • Use LIMIT with indexed columns in your WHERE and ORDER BY clauses for best performance
  • For large offsets (e.g., OFFSET 1000000), queries slow down significantly because MySQL still scans and discards all preceding rows. Use keyset pagination for better performance:
SELECT id, username, email FROM qa_users 
WHERE id > :last_id 
ORDER BY id 
LIMIT 50;

This approach uses the last row’s ID instead of an offset, which is much faster on large tables.

Database-Specific Alternatives

Different databases use different syntax:

  • PostgreSQL: LIMIT 10 OFFSET 20 (same as MySQL, but also supports FETCH NEXT 10 ROWS ONLY)
  • SQL Server: OFFSET 20 ROWS FETCH NEXT 10 ROWS ONLY (or older TOP 10 for just limiting without offset)
  • SQLite: LIMIT 10 OFFSET 20
  • Oracle Database: OFFSET 20 ROWS FETCH NEXT 10 ROWS ONLY (modern versions; older versions required subqueries with ROWNUM)

LIMIT with ORDER BY

Always combine LIMIT with ORDER BY when order matters:

SELECT * FROM qa_users ORDER BY username ASC LIMIT 5;

Without ORDER BY, the database returns 5 rows in whatever order is fastest internally — usually insertion order, but not guaranteed.

Getting the Total Count

When paginating, you typically need the total row count for UI purposes:

SELECT COUNT(*) as total FROM qa_users WHERE status = 'active';
SELECT id, username FROM qa_users WHERE status = 'active' LIMIT 50 OFFSET 0;

Run both queries: the first tells you how many pages exist, the second returns your paginated data.

Read more:

Similar Posts