LIMIT statements and basic usage in MySQL

LIMIT statements in MySQL

LIMITStatement forLimit the number of rows returned by the query, often used for pagination query or fetching part of data to improve query efficiency.

1. LIMIT syntax

SELECT List name1, List name2, ...
FROM Table name
WHERE condition
ORDER BY Sort fields
LIMIT Number of records OFFSET Offset;

or:

SELECT List name1, List name2, ...
FROM Table name
WHERE condition
ORDER BY Sort fields
LIMIT Offset, Number of records;

📌 LIMIT Offset, Number of RecordsandLIMIT Record Number OFFSET OffsetThe same effect.

2. Basic usage of LIMIT

(1) Get the first N rows of data

SELECT * FROM users LIMIT 5;

Return to tableusersThe front5 rows of data。

(2) LIMIT combined with ORDER BY

ifNot sorted, the returned data may beUnstable(Indefinite order).

SELECT * FROM users ORDER BY age DESC LIMIT 5;

Press firstage Sort descending, and then take the first 5 rows of data.

(3) LIMIT performs pagination query

If you want to getRecords 11 to 20：

SELECT * FROM users ORDER BY id ASC LIMIT 10 OFFSET 10;

or:

SELECT * FROM users ORDER BY id ASC LIMIT 10, 10;

📌 Equivalent toLIMIT 10 OFFSET 10,in:

• 10yesOffset (OFFSET), means skipping the first 10 data.
• 10yesNumber of rows returned (LIMIT)。

📌 Pagination query formula：

LIMIT Number of bars per page OFFSET (Current page - 1) * Number of bars per page;

For example:

• Page 1 (first 10):LIMIT 10 OFFSET 0
• Page 2 (Articles 11-20):LIMIT 10 OFFSET 10
• Page 3 (Articles 21-30):LIMIT 10 OFFSET 20

3. LIMIT combined with COUNT()

(1) Statistics total records

When paging, you usually need to get the total number of records first:

SELECT COUNT(*) FROM users;

📌 COUNT(\*)Faster, not ignoredNULL。

4. LIMIT combined with RAND() (random data fetch)

If you want3 records are returned randomly：

SELECT * FROM users ORDER BY RAND() LIMIT 3;

📌 ⚠ Attention：

ORDER BY RAND() Low performance, will sort all data and then get the value.

More efficient random query (foridIncremental data table):

SELECT * FROM users WHERE id >= (SELECT FLOOR(RAND() * (SELECT MAX(id) FROM users))) LIMIT 3;

5. Performance optimization of LIMIT statements

(1) LIMIT OFFSET may cause performance degradation

whenOFFSETWhen very large:

SELECT * FROM users ORDER BY id LIMIT 10 OFFSET 100000;

MySQL 100,000 rows will still be scanned, then discard the first 100,000 pieces of data, and only 10 pieces are returned.Influence performance。

Optimization method: replace with index plus conditionsOFFSET

SELECT * FROM users WHERE id > 100000 ORDER BY id LIMIT 10;

📌 Applicable toidIt is the case of auto-incrementing primary key, and the query speed is faster.

(2) LIMIT combined with JOIN may cause performance problems

SELECT * FROM users JOIN orders ON  = orders.user_id ORDER BY  LIMIT 100000, 10;

📌IfNo searchesIndicate, query will be very slow。

Optimization method：

SELECT users.* FROM users WHERE  > (SELECT id FROM users ORDER BY id LIMIT 100000, 1) ORDER BY  LIMIT 10;

FirstFind the starting point of the offset,AgainLIMITTake the value,Reduce the number of scanned rows。

6. LIMIT combined with GROUP BY

If you wantGet the top 3 users in each city：

SELECT * FROM (    SELECT *, ROW_NUMBER() OVER (PARTITION BY city ORDER BY age DESC) AS rank    FROM users) AS temp WHERE rank <= 3;

📌 ROW_NUMBER()MySQL 8.0+ is required to support it.

7. LIMIT combined with DISTINCT

If you wantThe first 5 items of unique data：

SELECT DISTINCT city FROM users LIMIT 5;

📌 LIMITexistDISTINCTAfter that, only 5 unique ones will be returnedcity。

8. LIMIT statement summary

🔥Key summary

• LIMITLimits the number of rows returned by the query, used to paginate or retrieve part of the data.
• LIMIT Offset, Number of RowsEquivalent toLIMIT Number of rows OFFSET Offset。
• ORDER BY + LIMITEnsure the data order is stable.ORDER BY RAND()Inefficient, it is recommended to use more optimized random query in big data tables.
• bigOFFSETQuery efficiency is low, it is recommended to use index optimization query.

In this way, yourLIMITQuerying will be faster and more efficient! 🚀

This is the end of this article about LIMIT statements and basic usage in MySQL. For more related contents of MySQL LIMIT statements, please search for my previous articles or continue browsing the related articles below. I hope everyone will support me in the future!