How to Use the MySQL LIMIT Clause
This tutorial explains how to use the MySQL LIMIT clause, which allows you to retrieve rows from one or more tables based on a specified number.
Why do You Need to Use MySQL LIMIT Clause?
Fetching rows from a large table may affect performance. It is not recommended to retrieve all rows from a table at once.
For example, suppose that the customer table has 1 million rows, and many client applications are trying to retrieve all those rows at once. It may be problematic for the database server to handle, as it will use many resources to retrieve the information and return the rows to the client application.
Of course, it depends on the database server. A high-performance server will return rows faster than a low-performance server.
However, it is a good practice to limit the number of rows in a large table to reduce the resources consumed by the server. To do so, you need to use the MySQL LIMIT clause in a SELECT statement.
LIMIT Clause Syntax
To limit the number of rows using the LIMIT clause, write a 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 top rows without conditions
The following example returns only the top two rows of the orders table:
SELECT * FROM orders
LIMIT 2;
Select only the top rows for the specified condition
The following SQL statement returns only the top three rows of the orders table where 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 criteria, MySQL will only return the first three rows of the matched rows in the table.
If less than three rows meet the specified criteria, MySQL will return all of them.
If there are no rows in the table, MySQL returns 0.
You can also specify additional conditions, as in the following example:
SELECT * FROM orders
WHERE (customer_id="#CU_300002" OR customer_id="#CU_300001") AND status="Open"
LIMIT 3;
LIMIT and ORDER BY
You can also use the LIMIT and ORDER BY clauses to select only the top rows of a specified table sorted in ascending or descending order by a specified column, as shown in the following example:
SELECT * FROM orders
ORDER BY order_date DESC
LIMIT 5.
In this example, MySQL will return only the top five rows sorted in descending order by column order_date.
Summary
In this tutorial, you have learned how to use the MySQL LIMIT clause. The LIMIT clause is useful when you want to limit the number of rows returned from a database.