> ## Documentation Index
> Fetch the complete documentation index at: https://villagesql.com/docs/llms.txt
> Use this file to discover all available pages before exploring further.

# MySQL LIMIT

> How to return only part of a MySQL result with LIMIT and OFFSET, the 2 forms of the clause, and why LIMIT without ORDER BY gives you arbitrary rows.

<Card title="VillageSQL is a drop-in replacement for MySQL with extensions." icon="database" href="/docs/mysql-8.4/stable/quickstart">
  All examples on this page work on VillageSQL. Install Now →
</Card>

`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

```sql theme={null}
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

```sql theme={null}
SELECT title FROM film ORDER BY title LIMIT 5;
```

```text theme={null}
+------------------+
| 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

```sql theme={null}
SELECT title FROM film ORDER BY title LIMIT 5 OFFSET 10;
```

```text theme={null}
+-----------------+
| 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.

```sql theme={null}
SELECT title FROM film ORDER BY title LIMIT 10, 5;
```

```text theme={null}
+-----------------+
| 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.

```sql theme={null}
SELECT title FROM film LIMIT 5;
```

```text theme={null}
+------------------+
| 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:

```sql theme={null}
EXPLAIN SELECT title FROM film LIMIT 5;
```

```text theme={null}
+----+-------------+-------+------------+-------+---------------+-----------+---------+------+------+----------+-------------+
| 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.

## See also

* [ORDER BY](/docs/tutorial/order-by) — deciding which rows LIMIT takes
* [Pagination in MySQL](/docs/guides/pagination) — why OFFSET gets slow, and what to use instead
* [Reading EXPLAIN in MySQL](/docs/guides/reading-explain) — the rest of what that plan row means