Microservices architecture and MySQL database pagination

Question

So imagine, I want to retrieve all orders for an array of customers. The arrayList in the example below will have an array of customer IDs.

This array will be passed into the get method below and processed asynchronously retrieving orders for each customer ID in the array.

Here's where I get lost. How can you paginate the database result set and pull only a small set of records at a time from the database without bring having to pull all the records across the network.

What's confusing me is the asynchronous nature as well as we won't know how many orders per customer there are? So how can you efficiently return a set page size at a time?

service.js

function callToAnotherService(id) {
    return new Promise((resolve, reject) => {
        //calls service passing id
    }
}

exports.get = arrayList => Promise.all(arrayList.map(callToAnotherService))
    .then((result) => result);

Answer 1

In MySQL there are more than one way to achieve this.

The method you choose depends on many variables, such your actual pagination method (whether you just want to have "previous" and "next" buttons, or actually want to provide range from 1...n, where n is total number of matching records divided by your your per page records count); also on database design, planned growth, partitioning and/or sharding, current and predicted database load, possible hard query limits (for example if you have years worth of records, you might require end user to choose a reasonable time range for the query (last month, last 3 months, last year, and so on...), so they don't overload the database with unrestricted and too broad queries, etc..

---

To paginate:

• using simple previous and next buttons, you can use the simple LIMIT [START_AT_RECORD,] NUMBER_OF_RECORDS method, as Rick James proposed above.
• using (all) page numbers, you need to know the number of matching records, so based on your page size you'd know how many total pages there'd be.
• using a mix of two methods above. For example you could present a few clickable page numbers (previous/next 5 for example), as well as first and last links/buttons.

If you choose one of the last two options, you'd definitely need to know the total number of found records.

As I said above, there is more than one way to achieve the same goal. The choice must be made depending on the circumstances. Below I'm describing couple simpler ideas:

• FIRST:
  If your solution is session based, and you can persist the session, then you can use a temporary table into which you could select only order_id (assuming it's a primary key in the orders table). Optionally, if you want to get the counts (or otherwise filter) per customer, you can also add the second column as customer_id next to order_id from the orders table.
  
  Once you have propagated the temporary table with minimum data, you can just easily count rows from the temporary table and create your pagination based on that number. Now as you start displaying pages, you only select the subset of these rows (using the LIMIT method above), and join the corresponding records (the rest of the columns) from orders on temporary table order_id.
  
  This has two benefits:
  1) Browsing records page-by-page would be fast, as it's not querying the (presumably) large orders table any more.
  2)You're not using aggregate queries on the orders table, as depending on the number of records, and the design, these would have pretty bad performance, as well as potentially impacting the performance of other concurrent users.
  
  Just bear in mind that the initial temporary table creation would be a bit slower query. But it'd definitely be even more slower if you didn't restrict temporary table to only essential columns.
  Still, it's really advisable that you set a reasonable maximum hard limit (number of temporary table records, or some time range) for the initial query
  
  

• SECOND: This is my favourite, as with this method I've been able to solve customers' huge database (or specific queries) performance issues on more than one occasion. And we're talking about going from 50-55 sec query time down to milliseconds. This method is especially immune to database scalability related slow downs.
  
  The main idea is that you can pre-calculate all kinds of aggregates (be that cumulative sum of products, or number of orders per customer, etc...). For that you can create an additional table to hold the aggregates (count of orders per customer in your example).
  
  And now comes the most important part:
  You must use custom database triggers, namely in your case you can use ON INSERT and ON DELETE triggers, which would update the aggregates table and would increase/decrease the order count for the specific customer, depending on whether an order was added/deleted. Triggers can fire either before or after the triggering table change, depending on how you set them up.
  Triggers have virtually no overhead on the database, as they only fire quickly once per (inserted/deleted) record (unless you do something stupid and for example run COUNT(...) query from some big table, which would completely defeat the purpose anyway)
  
  I usually go even more granular, by having counts/sums per customer per month, etc...
  
  When done properly it's virtually impossible for aggregate counts to go out of sync with the actual records. If you application enables order's customer_id change, you might also need to add ON UPDATE trigger, so the customer id change for order would automatically get reflected in the aggregates table.
  
  Of course there are many more ways you can go with this. But these two above have proven to be great. It's all depending on the circumstances...
  
  I'm hoping that my somewhat abstract answer can lead you on the right path, as I could only answer based on the little information your question presented...