MySQL Database learning



Home / SQL Statements / Select Statement / How to use LIMIT clause in MySQL?
Share this job:


How to use LIMIT clause in MySQL?

MySQL supports a LIMIT clause in SELECT, UPDATE, DELETE statements. In SELECT, the LIMIT clause tells the server to return only some of the rows selected by the statement. This is useful for retrieving records based on their position within the set of selected rows. LIMIT may be given with either one or two arguments:

LIMIT row_count
LIMIT skip_count, row_count

Each argument must be given as an integer constant. You cannot use expressions, user variables, and so forth. When followed by a single integer, row_count, LIMIT returns the first row_count rows from the beginning of the result set. To select just the first 10 rows of a result set, use LIMIT 10:

SELECT * FROM Country LIMIT 10;

When followed by two integers, skip_count and row_count, LIMIT skips the first skip_count rows from the beginning of the result set, and then returns the next row_count rows. To skip the first 20 rows and then return the next 10 rows, do this:

SELECT * FROM Country LIMIT 20,10;

The single-argument form of LIMIT is applicable only when the rows you want to retrieve appear at the beginning of the result set. The two-argument form is more general and can be used to select an arbitrary section of rows from anywhere in the result set.

When you need only some of the rows selected by a query, LIMIT is an efficient way to obtain them. For a client application that fetches rows from the server, you get better performance by adding LIMIT to the query than by having the client fetch all the rows and discard all but the ones of interest. By using LIMIT, the unwanted rows never cross the network at all.

It's often helpful to include an ORDER BY clause to put the rows in a particular order when you use LIMIT. When ORDER BY and LIMIT are used together, MySQL applies ORDER BY first and then LIMIT. One common use for this is to find the row containing the smallest or largest values in a particular column. For example, to find the row in a table t containing the smallest id value, use this statement:

SELECT * FROM t ORDER BY id LIMIT 1;

To find the largest value instead, use DESC to sort the rows in reverse:

SELECT * FROM t ORDER BY id DESC LIMIT 1;

The two-argument form of LIMIT is useful in conjunction with ORDER BY for situations in which you want to process successive sections of a result set. For example, in Web applications, it's common to display the result of a large search across a series of pages that each present one section of the result. To retrieve sections of the search result this way, issue a series of statements that all specify the same number of rows to return in the LIMIT clause, but vary the number of initial rows to skip:

SELECT * FROM t ORDER BY id LIMIT  0, 20;
SELECT * FROM t ORDER BY id LIMIT 20, 20;
SELECT * FROM t ORDER BY id LIMIT 40, 20;
SELECT * FROM t ORDER BY id LIMIT 60, 20;

It's possible to abuse the LIMIT feature. For example, it isn't a good idea to use a clause such as LIMIT 1000000, 10 to return 10 rows from a query that normally would return more than a million rows. The server must still process the query to determine the first million rows before returning the 10 rows. It's better to use a WHERE clause to reduce the query result to a more manageable size, and then use LIMIT to pull rows from that reduced result. This also makes the use of ORDER BY with LIMIT more efficient because the server need not sort as large a row set before applying the limit.

In UPDATE and DELETE statements, the LIMIT clause causes only a certain number of rows to be updated or deleted.





Select Statement related other questions

How can I limit the number of rows I want to fetch from my MySQL table?

MySQL provides LIMIT clause to use in the query to fetch desired number of rows from the database. So, you can use LIMIT clause in your query like this:SELECT buyer_id, buyer_name FROM buyer_table LIMIT 10 ...

How the select query is executed?

There should be no difference in quey execution. If there is an index on either column with good cardinality, then that index will probably be used to eliminate records first. If you are on mysql 5.0+ then mu ...

How to use SELECT statement to retrieve data?

The SELECT statement retrieves information from one or more tables. Retrievals tend to be the most common database operation, so it's important to understand how SELECT works and what you can do with it. ...

How to use ORDER BY to sort query results?

By default, the rows in the result set produced by a SELECT statement are returned by the server to the client in no particular order. When you issue a query, the server is free to return the rows in any conv ...

How to use LIMIT clause in MySQL?

MySQL supports a LIMIT clause in SELECT, UPDATE, DELETE statements. In SELECT, the LIMIT clause tells the server to return only some of the rows selected by the statement. This is useful for retrieving recor ...

How does DISTINCT work in MySQL?

DISTINCT helps to eliminate duplicates.  If a query returns a result that contains duplicate rows, you can remove duplicates to produce a result set in which every row is unique. To do this, include the ...

How to get aggregate results in MySQL?

A SELECT statement can produce a list of rows that match a given set of conditions. The list provides the details about the selected rows, but if you want to know about the overall characteristics of the rows ...