How to implement pagination with large datasets in MySQL efficiently?
Oct 16, 2025 pm 12:06 PM
Use keyset pagination instead of OFFSET-LIMIT for better performance. 1. First, query with ORDER BY and LIMIT: SELECT id, name FROM users ORDER BY id LIMIT 10. 2. Then, fetch next page using last seen value: SELECT id, name FROM users WHERE id > 100 ORDER BY id LIMIT 10. This avoids scanning skipped rows. Ensure the column used (e.g., id) is indexed: CREATE INDEX idx_users_id ON users(id). For multi-field sorting, use composite indexes. Avoid large OFFSETs like OFFSET 100000 LIMIT 10, which slow down queries. For infinite scroll, always prefer keyset pagination. If OFFSET is necessary, consider caching or precomputed pages. For analytical data, use summary tables updated periodically to reduce query load. Pre-aggregate data into daily or weekly summaries and query those instead of raw tables. This reduces I/O and improves response times.
Handling large datasets in MySQL efficiently requires smart pagination strategies to avoid performance bottlenecks. Traditional OFFSET-LIMIT pagination becomes slow as the offset grows, because MySQL still scans all rows up to the offset. Here’s how to optimize it.
Use Keyset Pagination (Cursor-Based)
Instead of OFFSET, use a unique, indexed column (like id or created_at) to fetch the next batch of results based on the last seen value.
Example:- First page: SELECT id, name FROM users ORDER BY id LIMIT 10;
- Next page: SELECT id, name FROM users WHERE id > 100 ORDER BY id LIMIT 10; (100 is the last ID from previous result)
This avoids scanning skipped rows and uses index lookups, making it much faster for deep pagination.
Ensure Proper Indexing
Keyset pagination only works efficiently if the column used in the WHERE and ORDER BY clauses is indexed.
- Create an index on the sorting column: CREATE INDEX idx_users_id ON users(id);
- If sorting by multiple fields (e.g., created_at id), use a composite index.
Without an index, even keyset queries will perform full table scans.
Avoid OFFSET with Large Offsets
OFFSET 100000 LIMIT 10 forces MySQL to skip 100K rows every time. This gets slower as the offset increases.
- For user-facing infinite scroll, prefer keyset pagination.
- If you must use OFFSET (e.g., random page access), consider caching or precomputing pages.
Consider Summary Tables for Analytics
If paginating aggregated or analytical data over millions of rows, generate summary tables updated nightly or hourly.
- Pre-aggregate data into daily/weekly summaries.
- Query the smaller summary table instead of raw data.
This reduces I/O and computation at query time.
Basically, replace OFFSET-LIMIT with keyset pagination where possible, keep indexes tight, and offload heavy work to background processes when needed.
The above is the detailed content of How to implement pagination with large datasets in MySQL efficiently?. For more information, please follow other related articles on the PHP Chinese website!
Hot AI Tools
Undress AI Tool
Undress images for free
Undresser.AI Undress
AI-powered app for creating realistic nude photos
AI Clothes Remover
Online AI tool for removing clothes from photos.
ArtGPT
AI image generator for creative art from text prompts.
Stock Market GPT
AI powered investment research for smarter decisions
Hot Article
Hot Tools
Notepad++7.3.1
Easy-to-use and free code editor
SublimeText3 Chinese version
Chinese version, very easy to use
Zend Studio 13.0.1
Powerful PHP integrated development environment
Dreamweaver CS6
Visual web development tools
SublimeText3 Mac version
God-level code editing software (SublimeText3)
How to format dates in MySQL?
Sep 19, 2025 am 02:06 AM
MySQL's DATE_FORMAT() function is used to customize the date and time display format. The syntax is DATE_FORMAT(date, format), and supports a variety of format characters such as %Y, %M, %d, etc., which can realize date display, group statistics and other functions.
How to use a CASE statement in MySQL?
Sep 20, 2025 am 02:00 AM
The answer is: MySQL's CASE statement is used to implement conditional logic in query, and supports two forms: simple and search. Different values ??can be dynamically returned in clauses such as SELECT, WHERE, and ORDERBY; for example, in SELECT, classification of scores by fractional segments, combining aggregate functions to count the number of states, or prioritizing specific roles in ORDERBY, it is necessary to always end with END and it is recommended to use ELSE to handle the default situation.
How to Automate MySQL Backups with a Script?
Sep 21, 2025 am 02:24 AM
Create a shell script containing the database configuration and mysqldump command and save it as mysql_backup.sh; 2. Store MySQL credentials by creating ~/.my.cnf file and set 600 permissions to improve security, modify the script to use configuration file authentication; 3. Use chmod x to make the script executable and manually test whether the backup is successful; 4. Add timed tasks through crontab-e, such as 02/path/to/mysql_backup.sh>>/path/to/backup/backup.log2>&1, realize automatic backup and logging at 2 a.m. every day; 5.
How to update a row if it exists or insert if not in MySQL
Sep 21, 2025 am 01:45 AM
INSERT...ONDUPLICATEKEYUPDATE implementation will be updated if it exists, otherwise it will be inserted, and it requires unique or primary key constraints; 2. Reinsert after deletion of REPLACEINTO, which may cause changes in the auto-increment ID; 3. INSERTIGNORE only inserts and does not repetitive data, and does not update. It is recommended to use the first implementation of upsert.
How to use the EXPLAIN command in MySQL?
Sep 18, 2025 am 01:48 AM
EXPLAINinMySQLrevealsqueryexecutionplans,showingindexusage,tablereadorder,androwfilteringtooptimizeperformance;useitbeforeSELECTtoanalyzesteps,checkkeycolumnsliketypeandrows,identifyinefficienciesinExtra,andcombinewithindexingstrategiesforfasterqueri
How to use subqueries in MySQL?
Sep 20, 2025 am 01:07 AM
Subqueries can be used in WHERE, FROM, SELECT, and HAVING clauses to implement filtering or calculation based on the result of another query. Operators such as IN, ANY, ALL are commonly used in WHERE; alias are required as derivative tables in FROM; single values ??must be returned in SELECT; related subqueries rely on outer query to execute each row. For example, check employees whose average salary is higher than the department, or add the company average salary list. Subqueries improve logical clarity, but performance may be lower than JOIN, so you need to ensure that you return the expected results.
How to calculate distance between two points in MySQL
Sep 21, 2025 am 02:15 AM
MySQL can calculate geographical distances through the Haversine formula or the ST_Distance_Sphere function. The former is suitable for all versions, and the latter provides easier and more accurate spherical distance calculations since 5.7.
How to handle timezones in MySQL?
Sep 20, 2025 am 04:37 AM
Use UTC to store time, set the MySQL server time zone to UTC, use TIMESTAMP to realize automatic time zone conversion, adjust the time zone according to user needs in the session, display the local time through the CONVERT_TZ function, and ensure that the time zone table is loaded.
