sql - How to make efficient pagination with total count -

we have web application helps organizing biological experiments (users describe experiment , upload experiment data). in main page, show first 10 experiments , below previous next 1 2 3 .. 30.

i bugs me how make efficient total count , pagination. currently:

select count(id) experiments; // not efficient in large datasets 

but how scale when dealing large datarecords > 200.000. tried import random experiments table, still performs quite ok (0.6 s 300.000 experiments).

the other alternative thought add addtional table statistics (column tablename, column recordscount). after each insert table experiments increase recordscount in statistics (this means inserting 1 table , updating other, using sql transaction of course). vice versa goes delete statement (recordscount--).

for pagination efficient way where id > last_id sql uses index of course. there other better way?

in case results filtered e.g. select * experiment name 'name%', option table statistics fails. need total count as: select count(id) experiment name 'name%'.

application developed using laravel 3 in case makes difference.

i develop pagination performs same. records count must not affect pagination nor total count of records.

please have query below:

 create procedure [getusers]     (      @inactive  bit = null,      @name   nvarchar(500),      @culture   varchar(5) = null,      @sortexpression varchar(50),      @startrowindex int,      @maxrowindex int,    @count int output       )         begin          select row_number()        on        (         order          case when @sortexpression = 'name' [user].[name] end,         case when @sortexpression = 'name desc' [user].[name] end desc         ) rowindex, [user].*        #tmptable       [user] (nolock)        (@inactive null or [user].[inactive] = @inactive)           , (@culture null or  [user].[defaultculture] = @culture)        , [user].name '%' + @name + '%'          select *      #tmptable (nolock)       #tmptable.rowindex > @startrowindex       , #tmptable.rowindex < (@startrowindex + @maxrowindex + 1)       select @count = count(*) #tmptable                if object_id('tempdb..#tmptable') not null  drop table #tmptable;     end