MySQL LIMIT

VillageSQL is a drop-in replacement for MySQL with extensions.

All examples on this page work on VillageSQL. Install Now →

LIMIT caps how many rows a query returns. It is what you reach for to look at the top of a large table, and it is the mechanism behind paging through results. Open the client with mysql -u root -p sakila before you start.

How LIMIT works

SELECT COLUMN_LIST
FROM TABLE_NAME
ORDER BY SORT_COLUMN
LIMIT ROW_COUNT OFFSET SKIP_COUNT;

Replace ROW_COUNT with how many rows you want and SKIP_COUNT with how many to skip first. LIMIT goes last, after ORDER BY. The OFFSET part is optional.

Examples

1) The first few rows

SELECT title FROM film ORDER BY title LIMIT 5;

+------------------+
| title            |
+------------------+
| ACADEMY DINOSAUR |
| ACE GOLDFINGER   |
| ADAPTATION HOLES |
| AFFAIR PREJUDICE |
| AFRICAN EGG      |
+------------------+
5 rows in set (0.00 sec)

The whole result is 5 rows, so the closing border and the count are the real end of it rather than a truncation.

2) Skip some rows first

SELECT title FROM film ORDER BY title LIMIT 5 OFFSET 10;

+-----------------+
| title           |
+-----------------+
| ALAMO VIDEOTAPE |
| ALASKA PHANTOM  |
| ALI FOREVER     |
| ALICE FANTASIA  |
| ALIEN CENTER    |
+-----------------+
5 rows in set (0.00 sec)

Rows 11 to 15 of the sorted list.

3) The 2 number form

MySQL also accepts the offset and the count as a comma separated pair, offset first. It means exactly the same thing.

SELECT title FROM film ORDER BY title LIMIT 10, 5;

+-----------------+
| title           |
+-----------------+
| ALAMO VIDEOTAPE |
| ALASKA PHANTOM  |
| ALI FOREVER     |
| ALICE FANTASIA  |
| ALIEN CENTER    |
+-----------------+
5 rows in set (0.00 sec)

The same 5 titles as example 2. The 2 numbers appear in the opposite order between the 2 forms, which is a reliable source of off by one confusion, so this tutorial writes LIMIT ROW_COUNT OFFSET SKIP_COUNT.

LIMIT without ORDER BY asks for arbitrary rows

Drop the ORDER BY and the query still runs.

SELECT title FROM film LIMIT 5;

+------------------+
| title            |
+------------------+
| ACADEMY DINOSAUR |
| ACE GOLDFINGER   |
| ADAPTATION HOLES |
| AFFAIR PREJUDICE |
| AFRICAN EGG      |
+------------------+
5 rows in set (0.00 sec)

Those are the same 5 titles the sorted query returned, and that is what makes this dangerous. Nothing in the statement asked for them in that order. Ask the server how it ran the query and it says why they came back that way:

EXPLAIN SELECT title FROM film LIMIT 5;

+----+-------------+-------+------------+-------+---------------+-----------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type  | possible_keys | key       | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+-------+---------------+-----------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | film  | NULL       | index | NULL          | idx_title | 514     | NULL | 1000 |   100.00 | Using index |
+----+-------------+-------+------------+-------+---------------+-----------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

That 1 warning is nothing to chase. EXPLAIN and EXPLAIN FORMAT=JSON attach Note 1003, which holds the query as the optimizer rewrote it, and SHOW WARNINGS prints it. The key column names idx_title, an index on title that already holds the column in sorted order, so reading the index is the cheapest way to answer. The rows arrived sorted as a side effect of that choice. Drop that index, add another column to the select list, or give the server more data, and the choice can change with no change to your query. LIMIT means “any rows” unless ORDER BY says which ones.

Summary

Use LIMIT to cap how many rows come back.
Add OFFSET to skip rows before taking them.
Prefer LIMIT ROW_COUNT OFFSET SKIP_COUNT to the comma form, which reverses the numbers.
Pair LIMIT with ORDER BY every time, or you have asked for arbitrary rows.

Getting Started

Server Development

Using Extensions

Creating Extensions

VillageSQL is a drop-in replacement for MySQL with extensions.

How LIMIT works

Examples

1) The first few rows

2) Skip some rows first

3) The 2 number form

LIMIT without ORDER BY asks for arbitrary rows

Summary

See also

VillageSQL is a drop-in replacement for MySQL with extensions.

​How LIMIT works

​Examples

​1) The first few rows

​2) Skip some rows first

​3) The 2 number form

​LIMIT without ORDER BY asks for arbitrary rows

​Summary

​See also

How LIMIT works

Examples

1) The first few rows

2) Skip some rows first

3) The 2 number form

LIMIT without ORDER BY asks for arbitrary rows

Summary

See also