MySQL provides a LIMIT clause that is used to specify the number of records to return. Returning a large number of records can impact on performance.
This feature is very helpful for optimizing the page loading time as well as to enhance the readability of a website.
In this tutorial, you will learn how to use MySQL LIMIT clause to constrain the number of rows returned by 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 makes it easy to code multi page results or pagination with SQL, and is very useful on large tables.
Following SQL query will select all records from 1 – 50 (inclusive) from a table called “Students”.
$sql = "SELECT * FROM Students LIMIT 50";
When the SQL query above is run, it will return the first 50 records.
The Limit Clause accepts one or two arguments which are offset and count. The value of both the parameters can be zero or positive integers.
- 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.
Offset is zero based. The offset of the first row is 0 (not 1).
Whenever two parameters are specified, the first is the offset and the second denotes the count.
Whenever only one parameter is specified, it denotes the number of rows to be returned from the beginning of the result set.
Syntax
SELECT column1, column2, ...
FROM table_name
LIMIT offset, count;
LIMIT clause explained.To retrieve the first three rows from the table “Students”, we will use the following query:
SELECT * FROM Students LIMIT 3;
To retrieve the rows 2-3(inclusive) from the table “Students”, we will use the following query:
SELECT * FROM Students LIMIT 1, 2;
To retrieve the rows 2-4 (inclusive) of a result set, you can use the following query:
SELECT * FROM Students LIMIT 1, 3;