In a sharded environment data will be splited to various machines/shards. I want to know how can I create a query that returns a paged results (ex 2nd page, 10 results or 10th page, 20 results)?
I know that it has to do with the primary key. With a single RDBMS it's easy because you have a auto-increment column so it's easy to get get the last 10 items and return paged data.
I work for ScaleBase, which is a maker of a complete scale-out solution an "automatic sharding machine" if you like, analyzes the data and SQL stream, splits the data across DB nodes, load-balances reads, and aggregates results in runtime – so you won’t have to!
You can see my answer to this thread about auto increment: Sharding and ID generation as instagram
Also, take a look on my post in http://database-scalability.blogspot.com/ about Pinterest, then and now...
Specifically - merging results from several shards to 1 result is HELL. May edge cases, GROUP BY, ORDER BY, JOINs, LIMIT, HAVING. I must say that in SB we support most of combinations, it took us ages. True, we need to do it generically, while you can "bend" to proprietary... but still...