How Does MySQL Process order by and limit in a Query?

In MySQL, the LIMIT clause is used with the SELECT statement to restrict the number of rows in the result set. The Limit Clause accepts one or two arguments that are offset and count. The value of both the parameters can be zero or positive integers.

LIMIT Clause:

Syntax:

SELECT column1, column2, …

FROM table_name

LIMIT offset, count;

The Limit clause accepts one or two parameters, whenever two parameters are specified, the first is the offset and the second denotes the count whereas whenever only one parameter is specified, it denotes the number of rows to be returned from the beginning of the result set.

• Offset: It is used to specify the offset of the first row to be returned.
• Count: It is used to specify the maximum number of rows to be returned.

SELECT with LIMIT and ORDER BY statement in MySQL:

Syntax:

SELECT expressions

FROM tables

[WHERE conditions]

[ORDER BY expression [ ASC | DESC ]]

LIMIT row_count_number;

• expressions: The columns or calculations that you wish to retrieve.
• tables: The tables that you wish to retrieve records from. There must be at least one table listed in the FROM clause.
• WHERE conditions:  These are optional conditions that must be met for the records to be selected.
• ORDER BY expression: These are optional statements used to return the result in ascending or descending order.
• LIMIT row_count_number: Specifies a limited number of rows to be returned based on row_count_number.

Let’s understand this syntax using an example. Say we have a relation, Student.

Create a database in MySQL:

Query:

-- create
CREATE TABLE Student (
  Id INTEGER PRIMARY KEY,
  name TEXT NOT NULL,
  score Number NOT NULL,
  branch TEXT 
);

Insert data into a table:

Query:

-- insert
INSERT INTO Student VALUES (55, 'Bhargavi', '81','civil');
INSERT INTO Student VALUES (56, 'Nikita', '75','IT');
INSERT INTO Student VALUES (57, 'Riddhi', '100','CSE');
INSERT INTO Student VALUES (58, 'Shreya', '94','civil');

Output:

Query:

SELECT * FROM Student ORDER BY Score DESC LIMIT 2;

Output:

The LIMIT operator can be used in situations such as the above, where we need to find the top 2 students having maximum scores and do not want to use any conditional statements. ORDER BY Score DESC has sorted the record in descending order and using LIMIT 2 we got the first 2 rows from the sorted results.

We can also include some situations using the WHERE clause in the above example. Suppose if we don’t want the Civil branch in our result set and want the first 2 students to have low Scores.

We can write queries like :

Query:

SELECT * FROM Student WHERE Branch != 'Civil' ORDER BY Score  LIMIT 2;

Output:

The above query will select all the students according to the imposed condition (i.e. all students except Civil branch students will be selected) then the results would be sorted by Score in ascending order (The ORDER BY keyword sorts the records in ascending order by default). Finally, the first 2 rows would be returned by the above query.