SQL requests for HTTP range response header

Question

I'm currently implementing a pagination based on HTTP range header on a Ruby on Rails application, and I'm wondering about SQL performances.

Unlike the traditional page and per_page query string params, the range header design allow the server to inform the client about the number of total result in the database. For example:

Content-Range: 0-4/123

Informing that:

the collection contains 5 records,
the database contains 123 records.

But I'm wondering about performances. If we use this pagination design, then we have to do 2 SQL requests:

1 request to get the 5 items,
1 request to get the total number of items.

Am I right? Or is there a way to find both informations without a downside?

You could probably hack away at a SQL query which selects both the items and the count but will it be significantly faster than two queries? — max, May 18 '15 at 13:48
You mean, with a sub-query? If you have an example, please show me. — Zag zag.., May 18 '15 at 13:57
`result = User.select('users.*, COUNT(id) OVER() AS tc').limit(5)`. `result.first.tc == User.count # true` However this is Postgres spefic. — max, May 18 '15 at 14:41
And it will mess up the normal count method and other things for the collection. — max, May 18 '15 at 14:43

score 0 · Accepted Answer · answered May 18 '15 at 14:45

In MySQL, you can use FOUND_ROWS() with SQL_CALC_FOUND_ROWS like this:

SELECT SQL_CALC_FOUND_ROWS * FROM tbl_name
WHERE id > 100 LIMIT 10;

Then to get the total number of rows if there weren't a limit:

SELECT FOUND_ROWS();

You don't save tons, but you can save some. And still, your mileage may vary.

SQL requests for HTTP range response header

1 Answers1