MySQL: Use of the LIMIT Keyword

MySQL: Use of the LIMIT Keyword

The LIMIT keyword is used to restrict the number of rows returned by a MySQL query. There are several situations where LIMIT can help MySQL reduce unnecessary processing.

1. LIMIT with ORDER BY

If you use LIMIT row_count with ORDER BY, MySQL can stop sorting once it has found the required number of rows instead of sorting the complete result. If the ordering can be done using an index, this can be very fast.

2. LIMIT with DISTINCT

When LIMIT is combined with DISTINCT, MySQL stops processing once it finds the required number of unique rows.

3. LIMIT with GROUP BY

In some cases, GROUP BY can be resolved by reading the key in order and calculating the required summaries. LIMIT can prevent MySQL from calculating unnecessary GROUP BY values.

4. LIMIT and Query Execution

Once MySQL has sent the required number of rows to the client, it can stop processing the query unless SQL_CALC_FOUND_ROWS is being used.

5. LIMIT 0

LIMIT 0 quickly returns an empty result set. This can be useful for checking whether a query is valid.

Example

SELECT * FROM dbName.emp LIMIT 0;

LIMIT 0 can also be useful with MySQL APIs when you need to determine the types of the result columns.

Example

SELECT * FROM dbName.emp LIMIT 10;

The above query retrieves only 10 records from the emp table.

Sometimes we need to retrieve records in batches. For example, if the emp table contains 100 records and we want to retrieve 10 records at a time, we can use LIMIT with an offset.

SELECT * FROM dbName.emp LIMIT 0, 10;

SELECT * FROM dbName.emp LIMIT 10, 10;

SELECT * FROM dbName.emp LIMIT 20, 10;

The first value specifies the starting position, and the second value specifies the number of records to return.

Case Study: Understanding LIMIT

I came across a question where someone felt that LIMIT was not working correctly. The issue was actually with understanding the meaning of the two parameters.The first parameter indicates the starting record. 

The first record starts at position 0. The second parameter specifies the number of records to return, not the ending record.

For example:

LIMIT 0, 10

This returns 10 records starting from position 0, which corresponds to records 0 through 9.

LIMIT 10, 20

This returns 20 records starting from position 10, which corresponds to records 10 through 29.

LIMIT 10, 10

This returns 10 records starting from position 10, which corresponds to records 10 through 19.

Example:

SELECT * FROM dbName.emp LIMIT 10, 10;

In short, remember:

LIMIT offset, row_count

The first value is the starting position, and the second value is the number of rows to return.