recentpopularlog in


« earlier   
Solutions for returning huge api responses - Google Groups
"Semantic pagination, as used by Stripe ( makes it easier to manage paging through a collection- if you just use simple integer offsets, then if your collection contents change while you are paging through you may end up off by one (or more) in either direction, resulting in duplicated or missing items between pages.

You can send HTTP Link headers with relation types of "prev" and "next" to give the client the URIs of the next (or previous) page so that they do not have to directly understand your pagination mechanism.  When there are no more results, simply do not include a "next" link.  That tells the client that no "next" page exists, so they must be at the end of the collection.
pagination  apis  design 
2 days ago by earth2marsh
Pagination Best Practices
Making good pagination is not a difficult thing. Really, you just want to remember the following basic guidelines, and you should be fine. We'll look at a large range of examples after that, to see what we can learn from existing pagination designs found on popular websites today.
pagination  best_practices  design  faruk_ateş 
5 days ago by mikemcauley
The SQL I Love <3. Efficient pagination of a table with 100M records | All You Need Is Backend
The main topic for the blog post was related to scanning a large table with 100 000 000 records using offset with a primary key (keyset pagination). Overall, 3 different approaches were reviewed and tested on the corresponding dataset. I recommend only one of them if you need to scan a mutable large table.

Also, we revised usage of EXPLAIN EXTENDED command to analyze execution plan of MySQL queries. I am sure that other RDBMS have analogs for the functionality.

In the next chapter, we will pay attention to data aggregation and storage optimization. Stay tuned!
pagination  sql 
7 days ago by hellsten
Pagination with OFFSET / FETCH : A better way
Pagination doesn't always have to be painful; SQL Server 2012 certainly makes the syntax easier, but if you just plug the native syntax in, you might not always see a great benefit. Here I have shown that slightly more verbose syntax using a CTE can lead to much better performance in the best case, and arguably negligible performance differences in the worst case. By separating data location from data retrieval into two different steps, we can see a tremendous benefit in some scenarios, outside of higher CXPACKET waits in one case (and even then, the parallel queries finished faster than the other queries displaying little or no waits, so they were unlikely to be the "bad" CXPACKET waits everyone warns you about).

Still, even the faster method is slow when there is no supporting index. While you may be tempted to implement an index for every possible sorting algorithm a user might choose, you may want to consider providing fewer options (since we all know that indexes aren't free). For example, does your application absolutely need to support sorting by LastName ascending *and* LastName descending? If they want to go directly to the customers whose last names start with Z, can't they go to the *last* page and work backward? That's a business and usability decision more than a technical one, just keep it as an option before slapping indexes on every sort column, in both directions, in order to get the best performance for even the most obscure sorting options.
pagination  sql 
7 days ago by hellsten
Efficient pagination of a table with 100M records | All You Need Is Backend
Compilation of my favorite SQL queries after a decade of dealing with relational databases. Today we talk about scanning a table with 100 million records.
sql  pagination  database  mysql 
7 days ago by spl
Five ways to paginate in Postgres, from the basic to the exotic
hen rows are deleted they leave holes in a page.
The order of the rows may not be meaningful. The database inserts new rows into holes left from deleted rows, which will cause the rows to be out of order.
“Where” clauses are not supported.
database  pagination 
12 days ago by ruairi

Copy this bookmark:

to read