php - Possible to limit results returned, yet leave 'pagination' to table? -

i building php site using jquery , datatables plugin. page laid out needs pagination working in dealing large datasets, have noticed server pulling returned rows opposed 10 row (can more) limit stated within each 'page'.

is possible limit results of query , yet keep id numbers of results in memory when page 2 hit (or result number changed) new data sought after?

does make sense way?

i dont want query db 2000 rows returned have 'front-end-plugin' make other results hidden when truthfully on page start.

the limit clause in sql has 2 parts -- limit , offset.

to first 10 rows:

select ... limit 0,10 

to next 10 rows:

select ... limit 10,10 

to next 10 rows:

select ... limit 20,10 

as long order result set same each time, absolutely don't have (and don't want to) first ask database send 2000 rows.

to display paging in conjunction this, still need know how many total rows match query. there 2 ways handle --

1) ask row count separate query

select count(*) table ... 

2) use sql_calc_found_rows hint in query, tell mysql calculate how many rows match query before returning 10 asked for. issue select found_rows() query result.

select sql_calc_found_rows column1, column2 ... limit 0,10 

2 preferable since not add query each page load.