SQL COUNT - greater than some number without having to get the exact count?

Question

There's a thread at https://github.com/amatsuda/kaminari/issues/545 talking about a problem with a Ruby pagination gem when it encounters large tables.

When the number of records is large, the pagination will display something like:

[1][2][3][4][5][6][7][8][9][10][...][end]

This can incur performance penalties when the number of records is huge, because getting an exact count of, say, 50M+ records will take time. However, all that's needed to know in this case is that the count is greater than the number of pages to show * number of records per page.

Is there a faster SQL operation than getting the exact COUNT, which would merely assert that the COUNT is greater than some value x?

What's DB client you are using.. Postgresql, MySql .. which one ? — Arup Rakshit
– Arup Rakshit, Commented Mar 31, 2015 at 18:10

xanatos · Accepted Answer · 2015-03-31 18:38:01Z

3

You could try with

SQL Server:

SELECT COUNT(*) FROM (SELECT TOP 1000 * FROM MyTable) X

MySQL:

SELECT COUNT(*) FROM (SELECT * FROM MyTable LIMIT 1000) X

With a little luck, the SQL Server/MySQL will optimize this query. Clearly instead of 1000 you should put the maximum number of pages you want * the number of rows per page.

edited Mar 31, 2015 at 18:38

answered Mar 31, 2015 at 18:07

xanatos

112k13 gold badges209 silver badges296 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Arup Rakshit Over a year ago

TOP function from which DB engine ? Do you have a generic solution, which can be used across the DB ?

Collectives™ on Stack Overflow

SQL COUNT - greater than some number without having to get the exact count?

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related