MySQL/ASP Paging, COUNT with LIMIT

I am trying to rebuild my page to go from old-school recordset paging, to LIMIT paging. This will stop my query trying to load 100,000 rows when it really doesn't have to.

The problem I am having, is getting the count of all the records for that search. Before, I was getting the number of results by doing this:

recordCount = rsPhotoSearch.RecordCount

But now I have limited my query to 30, to show 30 records per page, my 'recordCount' only shows 30, instead of the 100,000 that it should be.

So how am I supposed to get the total number of records for that search, without going through all 100,000 rows, which to me, seems to defy the use of the LIMIT method? I could probably run a separate query, the same, just to count the records - but is that practical, because that will also go though 100,000 rows to get a count?

Any开发者_JAVA百科 help gratefully received, as usual :)

From confused man

Hi. Create the query with SQL_CALC_FOUND_ROWS and then get it via FOUND_ROWS() function.
What I mean is this:

Set rsPhotoSearch = _
conn_Object.Execute("Select SQL_CALC_FOUND_ROWS * FROM table LIMIT offSet, rowCount")
Dim lngTotalRecords
lngTotalRecords = conn_Object.Execute("Select Found_Rows();")(0).Value
Response.Write "Total Records : "& lngTotalRecords 
'Loop starts
'etc

You don't need to get all rows. Just get the count of everything:

select count(*)
from (
    <whatever is your main query goes here, without LIMIT>
)

You quite are correct you will still have to scan your indexes to count the number of rows

I would question how useful showing the number of records is ?

if you calculate the record count yourself (rather than using SQL_CALC_FOUND_ROWS) you can limit the number of rows you scan and improve performance.