Introduction to MySQL Limit
The limit clause in Mysql is used torestrict the number of the rows retrieved in the resultset of the query to a certain count. It also helps us to retrieve the particular number of the records beginning from a particular 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 make the use of pagination technique and retrieve the result from the query in small chunks and display a certain number of the 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. Out of which one is required that is count_of_rows is required and the other one named 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 the integer value that helps us to specify the row count up to which the number of the records is to retrieve from the name_of_table table. The offset is the integer value that is optional and has the default value is 0. The offset is used for specifying the position of the record from where the result set is to be fetched from the table named name_of_table. The following figure will help you to understand the offset and row_count concepts clearly –
![LIMIT [offset,] count](https://cdn.educba.com/academy/wp-content/uploads/2020/06/LIMIT-offset-count.png)
Consider that the above diagram shows the records of the table and the numbers in it stand for the row number of that records. If the offset is specified as 3 then the row count that will begin will be from the fourth row as the offset begins from 0 by default. When the row_count is mentioned as 4 then starting from the defined offset i.e in this case from fourth row four records will be retrieved as the row_count is 4. If the row_count will have been 5 then beginning from the fourth record 5 rows would have been retrieved with row numbers 4,5,6,7 and 8.
Whenever we do not specify the offset value then the default value is considered as 0 and the row count begins from the 1-row number. In that case, both of the following limit clauses work in the same way-
LIMIT 5;
and
LIMIT 0,5;
Both of them will retrieve the first 5 records from the select query’s resultset.
Alternative Syntax:
The LIMIT clause has one more optional syntax that is provided in order to maintain the compatibility with the PostgreSQL syntax that is as follows –
LIMIT count_of_rows OFFSET offset;
Using LIMIT clause along with ORDER BY clause:
Whenever we retrieve the records without using the ORDER BY clause then the result is retrieved in any order and when the LIMIT clause is applied to such queries then the random rows with a limited number of row count will be required which is generally not what we want. The order in which the rows are retrieved plays an essential role in specifying what records are to be retrieved. It is a good practice to use the LIMIT clause along with the ORDER BY clause.
4.5 (2,642 ratings)
View Course
We can thus specify the ORDER BY clause with the field on which the ordering is to be done while retrieving the records and restrict the rows to be retrieved from the ordered data using the LIMIT clause. The order in which the execution of the clauses in the SELECT statement is considered is displayed in the below figure –
We can observe that all the clauses are executed before the LIMIT clause and finally while retrieving the resultset the last thing that is executed is to consider the row count and offset from the resultset that needs to be retrieved.
Examples of MySQL Limit
Following are the examples of mysql limit are given below:
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
Now, suppose that we want to retrieve the list of the 5 highest rated writers from the educba_writers table. For this, it is necessary to first order the resultset based on the rate column in the descending order so that the highest paid of them will be at 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 will first have to find out the number of records in the table. This can be found out 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 total records or page size. In our case, 16/5 that is 3.2 that means 4 pages will be required and the fourth page will contain only one record on it. After that we can specify the limit clause for the first 5 as LIMIT 5; for next 5 as LIMIT 5,5; for next as LIMIT 10,5 and so on.
Conclusion
We can restrict the number of records to be retrieved from the result set by using the LIMIT clause in MySQL. This clause also helps us to specify the offset from where the row counting should start while retrieval and specify the row count which tells how many rows are to be retrieved.
Recommended Articles
This is a guide to MySQL Limit. Here we also discuss the introduction and syntax of mysql limit along with different examples and its code implementation. you may also have a look at the following articles to learn more –
