For instance, if I must count your comments ought to owned by articles, it's apparent I don't have to cache your comments ought to total.

But let's say I wish to paginate a gallery (WHERE status = 1) that contains a million photos. Must I save that inside a table known as counts or Choose count(id) as total each time is okay?

Exist other solutions?

Please advise. Thanks.

For MySQL, you don't have to keep counts, you should use [cde] to prevent two queries.

E.g.,

SQL_CALC_FOUND_ROWS

In the manual:

In some instances, it's desirable to understand the number of rows the statement might have came back with no LIMIT, but without running the statement again. To acquire this row count, incorporate a SQL_CALC_FOUND_ROWS option within the Choose statement, after which invoke FOUND_ROWS() after.

Sample usage here.

It is dependent a little on the quantity of queries which are done on that table with a million records. Consider just being careful of excellent indexes, especially also multi-column indexes (since they're easily forgotton: here. That is going to do a great deal. And, make sure the queries become cached also well in your server.

If you are using this column very regular, consider saving it (whether it can not be cached by MySQL), as things can be slow. But the majority of the occasions good indexing will require proper care of it.

Best try: setup some tests to discover if your query can nonetheless be fast and gratifaction isn't shedding whenever you carry it out lots of occasions consecutively.

SELECT SQL_CALC_FOUND_ROWS * 
FROM Gallery
WHERE status = 1
LIMIT 10;
SELECT FOUND_ROWS();

Use that command (in MySQL) to obtain details about how a totally carried out and when it may be enhanced.

Doing the count each time could be OK.

Throughout paging, you should use SQL_CALC_FOUND_ROWS anyway

Note:

  1. A denormalied count will end up stale
  2. No-you will page a lot of products