MySQL Limit

Introduction to MySQL Limit

The limit clause in Mysql restricts the number of rows retrieved in the query resultset to a specific count. It also helps us to retrieve the particular number of records beginning from a specific offset from the MySQL table. Whenever the tables contain a huge amount of records, it becomes a heavy operation and even time-consuming to retrieve all these records and display them. This affects the performance of the application.

Hence, we can use the pagination technique to retrieve the result from the query in small chunks and display a certain number of records on each page. This can be done by using the limit clause. In this article, we will learn about the syntax of the limit clause and its usage, along with the examples in Mysql.

Syntax of MySQL Limit

The limit clause accepts two arguments. One of them, count_of_rows is mandatory, while the other, the offset is optional. The syntax of the LIMIT clause and the place where it should be used are shown below:

SELECT
selected_columns_or_expressions
FROM
name_of_table
LIMIT [offset,] count_of_rows;

The count_of_rows is an integer value specifying the number of records retrieved from the name_of_table table. The offset is the optional integer value with the default value of 0. The offset can be used to specify the position of the record from which the result set should be fetched from the table named name_of_table. The following figure will help you to understand the offset and row_count concepts clearly –

Consider that the above diagram shows the table’s records and its numbers represent the row number of that records. If the offset is specified as 3, the row count that will begin will be from the fourth row as the offset begins from 0 by default. If we set the row_count to 4 and define the starting offset as the fourth row, the database will retrieve four records as specified by the row_count. If we had set the row_count to 5, the database would retrieve five rows starting from the fourth record, with row numbers 4, 5, 6, 7, and 8.

If we don’t specify an offset value, the database considers the default value of 0, and the row count starts from the first row, which has a number of 1. In that case, both of the following limit clauses work in the same way-

LIMIT 5;

and

LIMIT 0,5;

They will retrieve the first five records from the select query’s resultset.

Alternative Syntax:

The LIMIT clause has an additional optional syntax to maintain compatibility with the PostgreSQL syntax. This syntax is as follows:

LIMIT count_of_rows OFFSET offset;

Using LIMIT clause along with ORDER BY clause:

If a user retrieves records without using the ORDER BY clause, the resulting set will be returned in any order. Applying the LIMIT clause to such queries will result in random rows being returned, which is generally not desirable. The order in which the rows are retrieved is essential in specifying what records will be retrieved. Using the LIMIT clause and the ORDER BY clause is a good practice.

To retrieve records in a specific order and restrict the number of rows returned, you can use the ORDER BY clause with the field on which you want to order the data, followed by the LIMIT clause. The figure below illustrates the order in which the clauses in a SELECT statement are executed:

We can observe that the database executes all the clauses before the LIMIT clause. When retrieving a result set, specify the row count and offset you want to retrieve.

Examples of MySQL Limit

The following examples illustrate the use of limits in MySQL:

Example #1

Let us create one table named educba_writers using the following query statement:

Code:

CREATE TABLE 'educba_writers' (
'id' int(11) NOT NULL,
'firstName' varchar(10) COLLATE latin1_danish_ci NOT NULL,
'rate' decimal(5,2) DEFAULT NULL,
'joining_date' date DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_danish_ci;

Output:

Example #2

Let us insert some more rows with non-null rate and joining date value –

Code:

INSERT INTO 'educba_writers' ('id', 'firstName', 'rate', 'joining_date') VALUES
(1, 'Payal', 750, "2020-05-01"),
(2, 'Vyankatesh', 700, "2020-01-01"),
(3, 'Omprakash', 600, "2020-02-01"),
(4, 'sakshi', 200, "2020-06-01"),
(5, 'prerna', 150, "2020-02-01"),
(6, 'preeti', 350, "2020-06-01"),
(7, 'sanjana', 400, "2020-02-01"),
(8, 'omkar', 450, "2020-06-01"),
(9, 'sohail', 650, "2020-02-01"),
(10, 'soniya', 850, "2020-06-01"),
(11, 'supriya', 700, "2020-02-01"),
(12, 'saniya', 750, "2020-06-01"),
(13, 'omkar', 410, "2020-02-01"),
(14, 'akshay', 910, "2020-06-01"),
(15, 'akash', 730, "2020-02-01"),
(16, 'siddharth', 980, "2020-06-01");

Output:

Example #3

Suppose we want to retrieve the list of the five highest-rated writers from the educba_writers table. For this, it is necessary to order the resultset based on the rate column in descending order so that the highest paid of them will be the first one to be retrieved. Further, as we have to retrieve only the top 5 writers, we will use the limit clause for specifying the same. Our query will be as follows –

Code:

select firstName, rate
FROM
educba_writers
ORDER BY rate DESC
LIMIT 5;

Output:

Example #4

We can even implement the pagination using the limit clause. For this, we must first determine the number of records in the table. Users can determine the number of rows in a table that satisfies a specific condition by using the COUNT() function in the following way:-

Code:

SELECT COUNT(*) from educba_writers;

Output:

Further, we need to consider the size of the page, that is, the number of records that will be displayed on a single page. Suppose, in my case page size is 5. The total number of pages will be the total records or page size. In this case, we can determine that we will need four pages because 16/5 equals 3.2, which means the fourth page will only have one record. After that, we can specify the limit clause for the first five as LIMIT 5; for the next five as LIMIT 5,5; for the next as LIMIT 10,5.

Conclusion

In MySQL, you can use the LIMIT clause to restrict the number of records retrieved from the result set. This clause also helps us to specify the offset from where the row counting should start while retrieval and select the row count, which tells how many rows are to be retrieved.

Recommended Articles

We hope that this EDUCBA information on “MySQL Limit” was beneficial to you. You can view EDUCBA’s recommended articles for more information.

1. IF Statement in MySQL
2. MySQL Timestamp
3. MySQL IN Operator
4. ROLLUP in MySQL