You’re Probably Doing Pagination Wrong

Рет қаралды 4,650

Жыл бұрын

📝 Get my free SQL Cheat Sheets: www.databasestar.com/get-sql-cheat-sheets/?
🎓 Learn and become confident in SQL: databasestar.mykajabi.com/sql-simplified/?
Pagination, or the technique of writing code to show pages of results, is a common feature of databases and applications.
Recent databases include keywords that let you perform pagination queries, such as showing 10 results at a time or showing the 5th page of results.
However, there are a couple of things to be aware of and to consider implementing, and without these, your query could show incorrect results and have performance issues.
So, watch this video to find out what these issues are and how to implement the solutions.
⏱ TIMESTAMPS:
00:00 - How to do pagination
01:03 - Issue 1 - performance
03:49 - Issue 2 - ordering
🔗 VIDEO LINKS:
Scripts in this video: github.com/bbrumm/databasestar/tree/main/videos/091_pagination

Пікірлер: 25

@jarman2904 10 ай бұрын

Really appreciate your advice, thank you very much for the time you spend doing those videos. It help me so much optimizing some databases I have. Best regards from the Dominican Republic

@DatabaseStar 10 ай бұрын

You're welcome! I'm glad you like the videos.

@invalidred 6 ай бұрын

Love your explanation style. Subscribed!

@DatabaseStar 6 ай бұрын

Thanks, glad you liked it!

@Dipubuet 8 ай бұрын

Wow! This is really tricky! Thank you very much for the video!

@DatabaseStar 8 ай бұрын

You’re welcome!

@cheo-studio 27 күн бұрын

Incredibly useful, thanks a lot.

@DatabaseStar 25 күн бұрын

Glad it was helpful!

@ebirimuchechukwuprecious3658 Жыл бұрын

I enjoyed this. Please make a video on cursor pagination (especially without an auto incrementing column)

@DatabaseStar Жыл бұрын

Thanks! Great suggestion!

@trietdangminh 3 ай бұрын

I have a complex JOIN query involving multiple tables. In the ORDER BY clause, I placed four columns from three different tables. I created indexes for these columns, but I'm still experiencing a decrease in performance. Upon examining the EXPLAIN output, I noticed that the indexes I created are being ignored, and a sequential scan is being performed instead. I suspect that the issue may be related to ordering by multiple columns from different tables. It would be appreciated if you could create a video addressing this topic.

@DatabaseStar 3 ай бұрын

Good idea for a video. Do you know where the expensive steps are? I mean, is it the retrieval of data from the tables or the order by or something else? Perhaps indexes on the columns being joined on could help improve performance - if you haven’t already done that!

@Delicatamente Жыл бұрын

Super!

@DatabaseStar Жыл бұрын

Thanks!

@paressh Жыл бұрын

Hi Friend, I need help with Oracle database installation in Intel MacBook Pro. I'm trying with Docker. I'm already Microsoft SQL using Docker. I have difficulty changing Oracle 23c XE password and Database login details. I don't know where I can find these. Please help me

@DatabaseStar Жыл бұрын

Hi, the login details for the docker image should be on the page that you downloaded the image from. I assume it's on the Oracle container registry. I haven't used this because I don't have an Intel MacBook (I have an M1 MacBook so the Docker images don't work for me).

@haydenmaines5905 Жыл бұрын

Writing this before watching: The way ive done pagination is theough LIMIT and OFFSET - Limit is set to number of results returned to page, Offset is number of results * current page number

@DatabaseStar Жыл бұрын

Yeah, that's the way I usually do it and what's mentioned in the video.

@haydenmaines5905 Жыл бұрын

@@DatabaseStar always nice to see I'm on the right track! :)