Paging - using ID, not LIMIT

[SLSCoder]

My reporting app dynamically creates a rather complex query including fields and a filter derived from client side user input.
It pulls records, 20 at a time so paging  will be used. Each report "record' will potentially include more than 1 row from the query results.
Of course, the query includes joins from multiple tables.
I have to use 'last id', not just LIMIT to determine where to start each subsequent page and use 'id' to determine how many rows to pull.
So, it'll look a bit like 'WHERE my_table.RecordID > 324 AND my_table.RecordID <= 357 <plus the filter>'.

One reason for paging is to speed the app up. Only display 20 records -> only pull 20 records.
Again, I can't just use LIMIT because each 'record' will potentially include more than one row.
Also, the result RecordIDs will not be sequential because of the filter. That is, if the last ID was 324 I can't query for records up to ID 344.

The only way I know to determine the correct RecordID range is to query the database using the filter and query ALL RecordIDs after the last ID and then count 20.
That's SLOW. I have to query the database twice,
• once querying for just the RecordID but ALL RecordIDs (not just 20 of them) after the last ID,
• count the first 20 and
• re-query the database and include all the fields and include the RecordID range I need for the 20 in the filter (as shown above).

My question: Is there a better way to do paging, given my circumstances?

 

[Barand]

Are you sure this is a database table and not a spreadsheet?

6 minutes ago, SLSCoder said:

because each 'record' will potentially include more than one row.

Can you show a sample of the data to ilustrate the problem? It's difficult to imagine why this might be the case.

[SLSCoder]

Barand: Thanks for responding:
No, it's not a spreadsheet - quote "the query includes joins from multiple tables."
I would think for an experienced database programmer it should be easy to see.
Again, the 'code' is generated dynamically and pretty complex but think about it:
imagine a report on companies, each with multiple employees. Each 'record' is a company including all its employees.
or a report on purchase orders, each order including multiple items. Each 'record' is an order including all its items.

[Barand]

I get it now. (I'll take the order example as I happened to have a convenient pair of test tables)

TABLE: oc_order
+----------+-----------+----------+------------+-----------------+
| order_id | firstname | lastname | date_added | order_status_id |
+----------+-----------+----------+------------+-----------------+
|        1 | Hugh      | Jass     | 2020-05-01 |               1 |
|        2 | Laura     | Norder   | 2020-05-03 |               1 |
|        3 | Tom       | DiCanari | 2020-05-05 |               1 |
|        4 | Peter     | Dowt     | 2020-05-07 |               1 |
+----------+-----------+----------+------------+-----------------+
4 rows in set (0.00 sec)

TABLE: oc_order_product
+------------------+----------+-------------+----------+
| order_product_id | order_id | name        | quantity |
+------------------+----------+-------------+----------+
|                1 |        1 | Gizmo       |        2 |
|                2 |        1 | Widget      |        5 |
|                3 |        1 | Wotsit      |        1 |
|                4 |        2 | Thingy      |        2 |
|                5 |        2 | Widget      |        1 |
|                6 |        2 | Thingumajig |        1 |
|                7 |        3 | Gizmo       |       20 |
|                8 |        3 | Widget      |       15 |
|                9 |        4 | Gizmo       |       25 |
|               10 |        4 | Thingy      |        5 |
|               11 |        4 | Wotsit      |       10 |
+------------------+----------+-------------+----------+

PROBLEM: to produce a paginated result with 2 orders per page

SOLUTION: Use a paginated subquery for the orders instead of the order table itself

PAGE 1

SELECT o.order_id
     , customer
     , p.name
     , p.quantity
FROM (
        SELECT order_id
             , concat(firstname, ' ', lastname) as customer
        FROM oc_order 
        ORDER BY order_id
        LIMIT 0, 2                                       -- <- paginate here
     ) o   
     JOIN oc_order_product p USING (order_id)
+----------+--------------+-------------+----------+
| order_id | customer     | name        | quantity |
+----------+--------------+-------------+----------+
|        1 | Hugh Jass    | Gizmo       |        2 |
|        1 | Hugh Jass    | Widget      |        5 |
|        1 | Hugh Jass    | Wotsit      |        1 |
|        2 | Laura Norder | Thingy      |        2 |
|        2 | Laura Norder | Widget      |        1 |
|        2 | Laura Norder | Thingumajig |        1 |
+----------+--------------+-------------+----------+

PAGE 2

SELECT o.order_id
     , customer
     , p.name
     , p.quantity
FROM (
        SELECT order_id
             , concat(firstname, ' ', lastname) as customer
        FROM oc_order 
        ORDER BY order_id
        LIMIT 2, 2 
     ) o   
     JOIN oc_order_product p USING (order_id)
+----------+--------------+--------+----------+
| order_id | customer     | name   | quantity |
+----------+--------------+--------+----------+
|        3 | Tom DiCanari | Gizmo  |       20 |
|        3 | Tom DiCanari | Widget |       15 |
|        4 | Peter Dowt   | Gizmo  |       25 |
|        4 | Peter Dowt   | Thingy |        5 |
|        4 | Peter Dowt   | Wotsit |       10 |
+----------+--------------+--------+----------+

 

[SLSCoder]

Thanks for responding - again.

The code is pretty complex due to the options given to the user but I think I can pull this off.
Once we simplified it to a basic orders -> items query it was much easier to see.

I'm kind of kicking myself for not seeing it. The solution you gave me is easy to implement.

Thanks again for your help.

[SLSCoder]

I've got a problem with this. The oc_order will have a filter on it and the fields don't match the fields being requested.
I realize I could make sure all the filter fields are included in the derived table but that gets involved and I'd rather not.

My intention is to reverse the JOIN and then add the whole table in another JOIN:

. . .
FROM oc_order_product INNER JOIN (SELECT order_id FROM oc_order ORDER BY order_id LIMIT 0, 2) AS tblLimit ON oc_order_product.order_id = tblLimit.order_id
                      INNER JOIN oc_order ON oc_order_product.order_id = oc_order.order_id
WHERE oc_order.lastname = 'Norder';

I could alternatively add the requested fields to the derived table, omit the second JOIN and write the filter like:
 

WHERE oc_order_product.order_id IN(SELECT order_id FROM oc_order WHERE oc_order.lastname = 'Norder')

I'm thinking that using the second JOIN will be at least as fast and the second JOIN would be easier for me.

Can I do it this way?
Is the second JOIN at least as fast as the alternative using IN?

 

[Barand]

I'd be inclined to do all the filtering required on the order in the subquery, ensuring there is only one row per order otherwise it throws the pagination. For example, if I only wanted to report orders that had sold widgets or gizmos

SELECT o.order_id
     , customer
     , p.name
     , p.quantity
FROM (
        SELECT DISTINCT
               o1.order_id
             , concat(firstname, ' ', lastname) as customer
        FROM oc_order o1
             JOIN oc_order_product op ON o1.order_id = op.order_id
                                      AND op.name IN ( 'gizmo' , 'widget' )
        ORDER BY order_id
        LIMIT 0, 2                                       -- <- paginate here
     ) o   
     JOIN oc_order_product p USING (order_id);

As for relative speeds of alternatives, benchmark them (example).

[SLSCoder]

Thank you Barand:

I'm surprised that the double JOIN on the oc_order_product table is faster than what I had in mind (not that I don't believe you 🙂 ).

The filter may include both tables and joins on both tables and even joins on the joins.
So, as you see, the need for speed is pretty critical.

To clarify, you're saying put the entire filter into the subquery?
I don't see why that would be a problem, just a bit more work for me lol.

 

[Barand]

31 minutes ago, SLSCoder said:

To clarify, you're saying put the entire filter into the subquery?

How do you expect me to answer questions like that when we don't have clue about what you are really doing. All we have is an abstraction of your problem. I have no idea what "filters" you are applying to other tables in the query. I am talking only about filters tht apply to "order" in this case.

35 minutes ago, SLSCoder said:

I'm surprised that the double JOIN on the oc_order_product table is faster than what I had in mind (not that I don't believe you 🙂 ).

I don't know for sure if it is - I can't run your queries and time them - but you can.

[SLSCoder]

Again, thanks.

I realize it's difficult to answer me. The problem is that about 1,000 lines of PHP code are used to generate the query.
The user creates the join tables and fields and well, it's complicated.
Most queries won't really be that big, just diverse. It depends on all the choices the user makes within the app.

As you suggest, I'll try different cases and test speeds.

Thanks again for your help.