MySQL LIMIT

In MySQL, you can use the LIMIT clause to limit the number of rows returned by the SELECT statement.

LIMIT syntax

This LIMIT clause can be used to limit the number of result set. LIMIT requires one or two non-negative numbers as parameters. Here are syntaxes of the LIMIT clause:

LIMIT [offset,] row_count;

LIMIT row_count OFFSET offset;

Here:

The results of the above two syntaxes are equivalent.
The offset specifies the offset of the first row to return. It is optional. When offset is not specified, the default value is 0. The offset starts from 0.
The row_count specifies the maximum number of rows to return.
The LIMIT clause locates the SELECT statement last generally.

For examples:

LIMIT 5

Return up to 5 rows. It is equivalent to LIMIT 0 5.
LIMIT 2 5

Return up to 5 rows from the 3rd row.

LIMIT and ORDER BY clauses

In the SELECT statement, you can use the LIMIT clause and the ORDER BY clauses together. For example, in the following use cases:

The 5 salespersons with the highest sales in a ERP system.
Top 10 articles with the highest click-through rate on a site.
The 10 most active members in the forum.
Pagination of articles on blog site

In these use cases, you all need to sort the rows first, and then fetch limited rows.

Here is a combination of LIMIT clauses and ORDER BY clauses Sample:

SELECT
    select_expression,...
FROM
    table_name
ORDER BY
    sort_expression,...
LIMIT [offset,] row_count;

LIMIT examples

In the following example, we use the film table int the Sakila sample database as a demonstration.

Find the 10 longest films

The following SQL statement returns the 10 longest films whose rating is ‘G’ from the film table.

SELECT
    film_id, title, length
FROM
    film
WHERE
    rating = 'G'
ORDER BY length DESC
LIMIT 10;

+---------+--------------------+--------+
| film_id | title              | length |
+---------+--------------------+--------+
|     212 | DARN FORRESTER     |    185 |
|     182 | CONTROL ANTHEM     |    185 |
|     609 | MUSCLE BRIGHT      |    185 |
|     597 | MOONWALKER FOOL    |    184 |
|     128 | CATCH AMISTAD      |    183 |
|     996 | YOUNG LANGUAGE     |    183 |
|      50 | BAKED CLEOPATRA    |    182 |
|     467 | INTRIGUE WORST     |    181 |
|     510 | LAWLESS VISION     |    181 |
|     612 | MUSSOLINI SPOILERS |    180 |
+---------+--------------------+--------+

Find the 10 shortest films

The following SQL statement returns the 10 shortest films whose rating is ‘G’ from the film table.

SELECT
    film_id, title, length
FROM
    film
WHERE
    rating = 'G'
ORDER BY length
LIMIT 10;

+---------+---------------------+--------+
| film_id | title               | length |
+---------+---------------------+--------+
|     247 | DOWNHILL ENOUGH     |     47 |
|     237 | DIVORCE SHINING     |     47 |
|       2 | ACE GOLDFINGER      |     48 |
|     575 | MIDSUMMER GROUNDHOG |     48 |
|     430 | HOOK CHARIOTS       |     49 |
|      83 | BLUES INSTINCT      |     50 |
|     292 | EXCITEMENT EVE      |     51 |
|     402 | HARPER DYING        |     52 |
|     794 | SIDE ARK            |     52 |
|     542 | LUST LOCK           |     52 |
+---------+---------------------+--------+

LIMIT in Pagination

Paging query is a very important application of LIMIT. For some large data tables, paging query can reduce database load and improve user experience.

The film table has 1000 rows, which we can inquiry by following COUNT(*) .

SELECT COUNT(*) FROM film;

+----------+
| COUNT(*) |
+----------+
|     1000 |
+----------+
1 row in set (0.00 sec)

If there is no paging query, all data is displayed on the page, which will cause the following problems:

Increase the performance consumption of the database
Increase in data volume during transmission
The experience of usres is not good, and too much data pair dazzles users.

If we use paging and displays 10 rows in a page, then 1000 rows need 100 ( 1000 / 10 ) pages to be displayed.

To query the first page, you only need to limit the maximum number of rows to 10. The SQL statement is as follows:

SELECT film_id, title FROM film LIMIT 10;

+---------+------------------+
| film_id | title            |
+---------+------------------+
|       1 | ACADEMY DINOSAUR |
|       2 | ACE GOLDFINGER   |
|       3 | ADAPTATION HOLES |
|       4 | AFFAIR PREJUDICE |
|       5 | AFRICAN EGG      |
|       6 | AGENT TRUMAN     |
|       7 | AIRPLANE SIERRA  |
|       8 | AIRPORT POLLOCK  |
|       9 | ALABAMA DEVIL    |
|      10 | ALADDIN CALENDAR |
+---------+------------------+

To query the second page, you need to skip the 10 rows on the first page and limit the maximum number of rows to 10. The SQL statement is as follows:

SELECT film_id, title FROM film LIMIT 10, 10;

+---------+------------------+
| film_id | title            |
+---------+------------------+
|       1 | ACADEMY DINOSAUR |
|       2 | ACE GOLDFINGER   |
|       3 | ADAPTATION HOLES |
|       4 | AFFAIR PREJUDICE |
|       5 | AFRICAN EGG      |
|       6 | AGENT TRUMAN     |
|       7 | AIRPLANE SIERRA  |
|       8 | AIRPORT POLLOCK  |
|       9 | ALABAMA DEVIL    |
|      10 | ALADDIN CALENDAR |
+---------+------------------+

Similarly, the SQL statement of the last page is as follows:

SELECT film_id, title FROM film LIMIT 990, 10;

Conclusion

In this article, you learned how to limit the number of rows to return using MySQL LIMIT clause. The main points of the LIMIT clause are as follows:

The LIMIT clause limits the number of rows to return.
LIMIT [offset,] row_count; is equivalent to LIMIT row_count OFFSET offset;.
The offset specifies the number of rows to skip. When offset is 0, it can be omitted.
The row_count specifies the maximum number of rows to return.
The LIMIT clause are often used for paging queries.
The LIMIT clause often works with ORDER BY.