You’re Probably Doing Pagination Wrong

📝 Get my free SQL Cheat Sheets: www.databasestar.com/get-sql-...
🎓 Learn and become confident in SQL: databasestar.mykajabi.com/sql...
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/databasesta...

[@jarman2904]

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]

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

[@Dipubuet]

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

[@DatabaseStar]

You’re welcome!

[@invalidred]

Love your explanation style. Subscribed!

[@DatabaseStar]

Thanks, glad you liked it!

[@ebirimuchechukwuprecious3658]

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

[@DatabaseStar]

Thanks! Great suggestion!

[@cheo-studio]

Incredibly useful, thanks a lot.

[@DatabaseStar]

Glad it was helpful!

[@trietdangminh]

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]

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!

[@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! :)

[@Delicatamente]

Super!

[@DatabaseStar]

Thanks!

[@karthiks4347]

Good👍

[@DatabaseStar]

Thanks!

[@rahulmittal2983]

Which application is this?

[@DatabaseStar]

This is pgAdmin, an SQL editor for Postgres.

[@chrisl.9750]

it's not dah-tah...
it's daaay-tah!

[@DatabaseStar]

Haha yeah I hear that sometimes (pronunciation of the word data)