How to Use the MySQL LIMIT Clause
In this tutorial, you'll learn how to use the LIMIT clause in MySQL to retrieve records from one or more tables in a given database based on a specified number.
Why do You Need to Use MySQL LIMIT Clause?
Retrieving records from a large table can have an impact on performance. You should not retrieve all records from the table at once.
For example, let's say you have 1 million records in the customer table. And a lot of client applications are trying to retrieve all those records at once at the same time. So it might be a problem for a database server to handle because your database server will use a lot of resources to search for the information and then return records to client applications.
Of course, it also depends on your database server, so a higher performance server returns records faster than a lower one.
However, it's a good practice to limit the number of records on a large table to reduce server consuming resources. That's why you need to use the MySQL LIMIT clause in the SELECT statement.
LIMIT Clause Syntax
To limit the number of records returned from the MySQL database using the LIMIT clause, you can write the SELECT statement as follows:
SELECT * FROM table
LIMIT number_of_records
LIMIT Clause Examples
Suppose you have a table named orders, which consists of five columns, order_number, customer_id, customer_name, amount, and status, as shown below:
| order_number | customer_id | customer_name | amount | status |
|---|---|---|---|---|
| #SO_300001 | #CU_300001 | Jackie Harmon | $1100.00 | Open |
| #SO_300002 | #CU_300002 | Iris Wilkerson | $1250.80 | Confirmed |
| #SO_300003 | #CU_300003 | Josh Warner | $1050.50 | Closed |
| #SO_300004 | #CU_300001 | Jackie Harmon | $1980.00 | Open |
| #SO_300005 | #CU_300004 | Jessica Day | $1870.50 | Open |
| #SO_300006 | #CU_300002 | Iris Wilkerson | $450.00 | Confirmed |
| #SO_300007 | #CU_300003 | Josh Warner | $780.00 | Closed |
| #SO_300008 | #CU_300004 | Jessica Day | $880.50 | Canceled |
| #SO_300009 | #CU_300005 | Iris Wilkerson | $850.50 | Canceled |
| #SO_300010 | #CU_300001 | Jackie Harmon | $1140.00 | Open |
Select only the Top Rows without Conditions
The following example retrieves only the top two rows of the orders table without conditions:
SELECT * FROM orders
LIMIT 2;
Select only the Top Rows with Specified Conditions
The following SQL statement returns only the top 3 rows of the orders table, which customer_id is equal to #CU_200002:
SELECT * FROM orders
WHERE customer_id="#CU_300002"
LIMIT 3;
Even though the orders table contains more than three rows that meet the specified condition, MySQL returns only the first three rows of the matched rows in the table.
If there are less than three rows that meet the specified condition, MySQL returns all those records.
MySQL returns 0 when there are no rows in the table.
You can also specify more conditions, as the following example shows:
SELECT * FROM orders
WHERE (customer_id="#CU_300002" OR customer_id="#CU_300001") AND status="Open"
LIMIT 3;
ORDER BY and LIMIT
You can also use the LIMIT clause and the ORDER BY clause to select only the top rows of a given table sorted in ascending order or in descending order by a given column, as the following example shows:
SELECT * FROM orders
ORDER BY order_date DESC
LIMIT 5.
In the above example, MySQL returns only the top 5 rows sorted in descending order by the column order_date.
In this tutorial, you've learned how to use the MySQL LIMIT clause in the SELECT statement to limit the number of rows returned from the MySQL database.