MySQL LIMIT to Constrain the number

We can use the MySQL LIMIT statement in order to achieve an exact array of results in a query. This clause can provide us with the MIN and MAX records, as well as the second maximum, fourth minimum record etc.. In this article, we look at examples of how to use the MySQL LIMIT statement to achieve these results.

Syntax

The MySQL LIMIT statement, when used with a SELECT statement, limits results up to the needed number of rows. The LIMIT statement consists of either one or two parameters.

Important:

Arguments must be integers, not null and should be greater than or equal to zero.

Fig 1. Syntax of the MySQL LIMIT statement

LIMIT parameters

• OFFSET – parameter that specifies the initial number of rows in a query that will be offset, or excluded from the results
• COUNT – Parameter that specifies the maximum number of rows that will be returned in a query

Fig 2. LIMIT count parameter.

The same query can be built with a LIMIT statement which uses two arguments Fig. 3.

Fig 3. LIMIT count with two arguments

Best practices when using the LIMIT and OFFSET clause

Usually, the LIMIT statement is used with an ORDER BY statement. Effect: ORDER BY sorts the records from the maximum value to the minimum value (DESC) or vice versa (ASC). In addition, we use LIMIT to return a defined number of records.

In the following example, the table users will be the basis for the example queries using the LIMIT and OFFSET clause.

Table: users

We can also use the LIMIT clause to receive the lowest or highest results from a table. Let us observe the example on Fig. 4 where database returns 2 users with the highest id in table users.

We set DESC sorting to begin from the biggest values. Our coding routine looks as follows:

Fig 4. Example Limit

The output from table users:

Bias with LIMIT

In the following example in Fig. 5, the database returns 6 users with the highest id in table users. However, the first 2 id values will be ignored and the query returns next 6 records, starting from 3 (we will receive from 3 to 8 records).

Fig 5. Limit with two arguments, first version

Fig 6. Limit with two arguments, second version

The following shows the output from table user according to Fig. 5 and Fig. 6.

Speed of processing in LIMIT

The MySQL LIMIT statement normally performs fast, but if large values are set in the LIMIT clause, this can decrease performance substantially. For example when we set the LIMIT clause like in Fig. 7, the query will be processed quickly. However, specifying a very large value like in Fig. 8, we will notice that it will take some time to execute.

Fig 7. Fast-processed LIMIT.

Fig 8. Slowly-processed LIMIT.

The Nth highest record with LIMIT in MySQL

If the selection of maximal and minimal records from the database is well-defined, we can use the MySQL LIMIT clause for some special cases. For instance: we have table materials with columns id, price, weight. Our task is to find the material with the third highest price from materials (Fig. 9).

Table: Materials

Fig 9.  nth     LIMIT EXAMPLE.

The parameter n refers to the nth record number that we need. For example, if we need the 3rd record, we set n = 3. Let us observe the following example with precise integers.

Task: Get the third most expensive material (n = 3).

Fig 10. Third most expensive material.

The output from the query on table materials:

Other Usage of the MySQL LIMIT and OFFSET clause

In several applications, the LIMIT clause can be used to achieve the following:

Limitation for retrieving a unique record

When writing queries, we can identify how many records we need and this can be achieved by using the WHERE clause. However, this takes more time to process and develop. In such cases, a LIMIT statement is better because it accomplished the same requirement efficiently. For instance, if we have LIMIT 1 then a table scan stops after one record is found instead of scanning the whole table.

Division of big queries DELETE or INSERT

Sometimes we must impose data limits on large queries involving a DELETE or INSERT statement, to avoiding high web traffic. A large query can cause the application to hang, and cause errors in the application. When deleting or insert a large number of records, the LIMIT statement allows us to execute these operations partially.