DEV Community πŸ‘©β€πŸ’»πŸ‘¨β€πŸ’»

DEV Community πŸ‘©β€πŸ’»πŸ‘¨β€πŸ’» is a community of 968,547 amazing developers

We're a place where coders share, stay up-to-date and grow their careers.

Create account Log in

Discussion on: 1 SQL Query You Should Stop Using

Collapse
 
patricknelson profile image
Patrick Nelson

Did you have any performance charts on the second technique outlined? Curious to know if having a slightly more complex WHERE clause with two sorted columns affects performance much when dealing with ~2M rows.

Also: Was this MySQL? I wonder if different databases have similar results. Worth noting in case results vary between the SQL DB's out there. Since I'm lazy and I'd be interested in tinkering with this first hand, do you still have the source code you used to generate these stats? Specifically, the code used to generate the fake data (e.g. script inserting output from Faker)?

Thanks for the article!

Collapse
 
abdisalan_js profile image
Abdisalan Author

Found my code and added it to GitHub here github.com/Abdisalan/blog-code-exa...

I've also included the db schema sql file to help create the database. Also, each file is named after what pagination method they use and I collected the data by outputting the times into a file like this:

python offset_pagination.py > data.csv
Collapse
 
abdisalan_js profile image
Abdisalan Author

The performance when sorting with two columns was slightly slower, running at 425k rows per second vs 476k. I don’t have the charts for that one unfortunately, I should have made it before I deleted the DB!

The database was in PostgreSQL, I wonder if another db would perform better πŸ€”

The script is also gone! 😒 I can try to recreate it and get back to you though.

Thanks for reading!