EDUCBA

Home » Data Science » Data Science Tutorials » MySQL Tutorial » MySQL Limit

MySQL Limit

Introduction to MySQL Limit

The limit clause in Mysql is used to restrict the number of 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.

Start Your Free Data Science Course

Hadoop, Data Science, Statistics & others

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

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.

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 –

SELECT statement

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 limits 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:

MySQL Limit-1.1

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");

MS SQL Training (16 Courses, 11+ Projects)

4.9

Oracle Training (14 Courses, 8+ Projects)

4.8

PL SQL Training (4 Courses, 2+ Projects)

4.7

Output:

MySQL Limit-1.2

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:

MySQL Limit-1.3

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:

MySQL Limit-1.4

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 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 the 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 –

  1. IF Statement in MySQL
  2. MySQL Timestamp
  3. MySQL IN Operator
  4. ROLLUP in MySQL
0 Shares
Share
Tweet
Share