Knowing very little about sqlite wizardry, could you utilize a COUNT function to determine total number of rows, then use CTEs to return the data in sets eg. If rpm_packages returns 15k rows and the rpm_db chokes when trying to return all at once, could you partition the result-set using row_id and then reassemble afterwards with a UNION? (eg.

WITH rpm_result_set1 AS (SELECT * FROM rpm_packages WHERE rowid > 0 AND rowid < 1000), rpm_result_set2 AS (SELECT * FROM rpm_packages WHERE rowid >= 1000 AND rowid <= 2000)... SELECT * FROM rpm_result_set1 UNION ALL SELECT * FROM rpm_result_set2

)

I'd say this counts as wizardry... 🧙🏽‍♂️

This would depend on the implementation of the rpm_packages table. Is it able to optimize the query using the rowid?

Good point. It does index on "name" column. Might be doing table scans for each sub select. I would be interested in seeing the comparison.

If that's the case I think this query will perform dramatically worse because it will force multiple full reads that are later filtered by the sqlite engine.

I’d bet that it’s indexed on name, because if you give it a name, it’ll fetch the rpm info for that name. I would be surprised if there was any kind of cleverness in trying to do pagination.

Thanks for chiming in guys, I figured it was a long shot but worth mentioning as a possibility 🙂

thanks all, at least i learned something new 😄