Tracking a total count of items over a series of paged results

What is the ideal way to keep track of the total count of items when dealing with paged results?

This seems like a simple question at first but it is slightly more complicated (to me... just bail now if you find this too stupid for words) when I actually start thinking about how to do it efficiently.

I need to get a count of items from the database. This is simple enough. I can then store this count in some variable (a $_SESSION variable for instance). I can check to see if this variable is set and if it isn't, get the count again. The trick part is deciding what is the best way to determine when I need to get a new count. It seems I would need to get a new count if I have added/deleted items to the total or if I am reloading or revisiting the grid.

So, how would I decide when to clear this $_SESSION variable? I can see clearing it and getting a new count after an update/delete (or even adding or subtracting to it to avoid the potentially expensive database hit) but (here comes the part I find tricky) what about when someone navigates away from the page or waits a variable amount of time before going to the next page of results or reloads the page?

Since we may be dealing with tens or hundreds of thousands of results, getting a count of them from the database could be quite expensive (right? Or is my assumption incorrect?). S开发者_如何学运维ince I need the total count to handle the total number of pages in the paged results... what's the most efficient way to handle this sort of situation and to persist it for... as long as might be needed?

BTW, I would get the count with an SQL query like:

SELECT COUNT(id) FROM foo;

I never use a session variable to store the total found in a query, I include the count in the regular query when I get the information and the count itself comes from a second query:

// first query
SELECT SQL_CALC_FOUND_ROWS * FROM table LIMIT 0, 20;
// I don´t actually use * but just select the columns I need...

// second query
SELECT FOUND_ROWS();

I´ve never noticed any performance degradation because of the second query but I guess you will have to measure that if you want to be sure.

By the way, I use this in PDO, I haven´t tried it in plain MySQL.

Why store it in a session variable? Will the result change per user? I'd rather store it in a user cache like APC or memcached, choose the cache key wisely, and then clear it when inserting or deleting a record related to the query.

A good way to do this would be to use an ORM that does it for you, like Doctrine, which has a result cache.

To get the count, I know that using COUNT(*) is not worse than using COUNT(id). (question: Is it even better?)

EDIT: interesting article about this on the MySQL performance blog

Most likely foo has a PRIMARY KEY index defined on the id column. Indexed COUNT() queries are usually quite easy on the DB.

However, if you want to go the extra mile, another option would be to insert a special hook into code that deals with inserting and deleting rows into foo. Have it write the number of total records into a protected file after each insert/update and read it from there. If every successful insert/update gets accounted for, the number in the protected file is always up-to-date.