Đừng sử dụng “offset” trong SQL của bạn

Nguồn: https://www.youtube.com/watch?v=WDJRRNCGIRs

Tác giả: Hussein Nasser

Ngày xuất bản: 2020-12-30T00:00:00

Length: 09:10

In this video I’ll explain why you should avoid using SQL offset when implementing any kind of paging. I’ll explain what offset does, why is it slow and what is the alternative for better performance

This video is inspired by Use the index luke, i’ll have a link to the blog and slides to learn more.

Let say you have a web application with an API that supports paging, you user want to request 10 news articles in page 10, this is performed via a simple GET request as shown here

The API server receives the GET request and builds the SQL in order to send it to the database hopefully a pool of connections exist here.

Page 10 translates to offset 100 assuming each page has 10 records and now the database is ready to execute the query against the table.

Offset by design means fetch and drop the first x number of rows, so in this case the database will fetch the first 110 rows and physically drop the first 100 leaving the limit of 10 which the user will get. As the offset increase, the database is doing more work which makes this operation extremely expensive.

Furthermore, the problem with offset is you might accidentally read duplicate records. consider the user now want to read page 11 and meanwhile someone inserted a new row in the table, row 111 will be read twice

Let us jump and test this against postgres

0:00 Why Offset is Bad

2:30 Offset Example

5:45 Offset alternative

Use the Index Luke Blog

https://use-the-index-luke.com/no-offset

Slides in this video

https://payhip.com/b/B6o1

(Members get all slides for free. Become a Member to support the channel)

https://www.youtube.com/channel/UC_ML5xP23TOWKUcc-oAE_Eg/join

🎙️Listen to the Backend Engineering Podcast

https://husseinnasser.com/podcast