Is MySQL LIMIT applied before or after ORDER BY?

MySQLMySQLi Database

The MySQL LIMIT is applied after ORDER BY. Let us check the limit condition. Firstly, we will create a table −

mysql> create table LimitAfterOrderBy
   −> (
   −> Id int,
   −> Name varchar(100)
   −> );
Query OK, 0 rows affected (0.50 sec)

Insert some records in the table using insert command. The query is as follows −

mysql> insert into LimitAfterOrderBy values(101,'John');
Query OK, 1 row affected (0.18 sec)

mysql> insert into LimitAfterOrderBy values(102,'Carol');
Query OK, 1 row affected (0.15 sec)

mysql> insert into LimitAfterOrderBy values(103,'Bob');
Query OK, 1 row affected (0.21 sec)

mysql> insert into LimitAfterOrderBy values(104,'Sam');
Query OK, 1 row affected (0.15 sec)

Display all records from the table using select statement. The query is as follows −

mysql> select *from LimitAfterOrderBy;

The following is the output −

+------+-------+
| Id   | Name  |
+------+-------+
| 101  | John  |
| 102  | Carol |
| 103  | Bob   |
| 104  | Sam   |
+------+-------+
4 rows in set (0.00 sec)

Here is the query when LIMIT comes after ORDER BY −

mysql> select *from LimitAfterOrderBy order by Name limit 4;

The following is the output −

+------+-------+
| Id   | Name  |
+------+-------+
|  103 | Bob   |
|  102 | Carol |
|  101 | John  |
|  104 | Sam   |
+------+-------+
4 rows in set (0.00 sec)
raja
Published on 16-Jan-2019 10:21:20
Advertisements