pagination   4181

« earlier    

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ş 
2 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 
4 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 
4 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 
4 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 
9 days ago by ruairi

« earlier    

related tags

%tech_bz↗  ajax  api  automatic  best_practices  bestpractices  blog  bookmarks_bar  cards  client  css  cursor  custom  custompost  data  database  datatables  db  design  dj-pagination  django  ecto  elixir  elm  engagement  entityframework  enumerator  etsy  events  example  extension  facebook  facetwp  faruk_ateş  filter  filtering  flatlist  floss  fork  forum  function  gem  genesis  genesiswp  github  gitlab  google  graph-ql  graphql  guide  guidelines  howto  ifttt  index  infinite  infinitescroll  interesting  javascript  jekyll  jquery  jquery_ui_lib  js  json  justinmind  keyset  laravel  laravel5.4  laravel5  latex  lazy.loading  lazy  ldap  learning  lib_-_javascript  library  links  list  listview  load  loop  loops  material  materialdesign  mongodb  mysql  next  normalizr  offset  opensource  page  pages  paging  pattern  performance  perl  phoenix  pinterest  plugin  pocket  postgres  postgresql  profiling  programming  python-gitlab  python  query  rails  react-native  react-router  react  reactjs  realtime  record  reddit  redux  reference  relay  requests  rest  ruby  rubyonrails  scrivener  scroll  scrolling  search  seo  sequences  slack  sort  sorting  sql  stackoverflow  steppers  steps  strategies  styleguide  suggestion  t_dev  tables  techniques  template  tests  thc  tip  toread  troubleshoot  tutorial  twitter  ui  urls  ux  varibale  views  web  webapps  webmaster  wordpress 

Copy this bookmark: