MySQL ดึงข้อมูลที่เรียงแล้วหลังจากข้าม Limit offset (Pagination)

ใช้ Subquery สำหรับ Pagination แบบ Custom

เมื่อต้องการข้อมูลที่เรียงแล้ว แต่เริ่มจาก Offset ที่กำหนด (ไม่ใช่หน้าแรก) ใช้ Subquery เพื่อ Filter ก่อน แล้วค่อย Sort/ Limit

ตัวอย่าง: ข้าม 4 แถวแรก แล้วเอา 4 แถวถัดไป เรียง DESC

CREATE TABLE DemoTable618 (
    StudentId INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    StudentFirstName VARCHAR(100)
);

INSERT INTO DemoTable618(StudentFirstName) VALUES
('David'), ('Chris'), ('Robert'), ('Sam'), ('Mike'), ('Carol'), ('Bob'), ('John');

Query: StudentId >= 5, เรียง ASC, LIMIT 4, แล้วเรียงกลับ DESC

SELECT * FROM (
    SELECT StudentId, StudentFirstName 
    FROM DemoTable618 
    WHERE StudentId >= 5 
    ORDER BY StudentId 
    LIMIT 4
) AS tbl
ORDER BY tbl.StudentId DESC;

ผลลัพธ์:

+-----------+------------------+
| StudentId | StudentFirstName |
+-----------+------------------+
| 8         | John             |
| 7         | Bob              |
| 6         | Carol            |
| 5         | Mike             |
+-----------+------------------+

อธิบาย Logic

1. Inner Query: Filter WHERE StudentId >= 5 → เหลือ Id 5,6,7,8
2. Inner Query: ORDER BY StudentId LIMIT 4 → เอา 5,6,7,8 (เรียง ASC)
3. Outer Query: ORDER BY StudentId DESC → กลับลำดับเป็น 8,7,6,5

Standard Pagination Pattern (OFFSET/LIMIT)

-- Page 1 (Offset 0, Limit 10)
SELECT * FROM DemoTable618 ORDER BY StudentId LIMIT 10 OFFSET 0;

-- Page 2 (Offset 10, Limit 10)
SELECT * FROM DemoTable618 ORDER BY StudentId LIMIT 10 OFFSET 10;

-- Page N
SELECT * FROM DemoTable618 ORDER BY StudentId LIMIT 10 OFFSET (page-1)*10;

⚠️ ปัญหา OFFSET ใหญ่ (Deep Pagination)

OFFSET 1,000,000 จะ Scan 1 ล้านแถวก่อน Skip → ช้ามาก

แก้ไข: Keyset Pagination (Seek Method)

-- แทน OFFSET ใช้ WHERE บนคอลัมน์ที่ Index
-- Page 1
SELECT * FROM DemoTable618 ORDER BY StudentId LIMIT 10;

-- Page 2 (ใช้ Last ID จาก Page 1 = 10)
SELECT * FROM DemoTable618 WHERE StudentId > 10 ORDER BY StudentId LIMIT 10;

-- Page 3 (Last ID = 20)
SELECT * FROM DemoTable618 WHERE StudentId > 20 ORDER BY StudentId LIMIT 10;

ข้อดี: ใช้ Index Seek ตรงไปหน้าที่ต้องการ ไม่ Scan Offset

Cursor-based Pagination (สำหรับ API)

-- Client ส่ง Cursor (Last ID) มา
SELECT * FROM DemoTable618 
WHERE StudentId > ?  -- cursor
ORDER BY StudentId 
LIMIT 20;