CODEWITHSUNDEEP
sqlruby-on-railsruby
isthmuses
isthmuses

Reputation: 1336

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

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?

Upvotes: 0

Views: 936

Answers (1)

xanatos
xanatos

Reputation: 111850

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.

Upvotes: 3

Related Questions