Pagination: count/LIMIT queries or just one?

[Warboss_Alex]

I've got a PHP class for paginating results of a database query, with page numbers and all the usual trimmings, but there's only one query involved - getting the number of pages for the pagination links, and then navigating through the query to get the results in the area I want.
This involves fetching ALL the records in the table into memory though, and I'm concerned about the overhead this incurs. It's not the scripting, it's more the system architecture I guess .. would having all the records in memory perform better than doing a count(*) query on the data, and then using LIMIT (a second query) to only actually pull the required data into memory.

I'm sure there's arguments for and against, especially when the database gets big. But is there a winner, between the two? I've always tended towards the one-query method because you need the record count anyway, and even in a LIMIT query, MySQL has to traverse all the rows anyway?

Can anyone give me some advice? thanks in advance!

Cheers,
Alex ...

[r937]

with a LIMIT query, mysql does not have to read all the rows if the ORDER BY is on columns that have indexes

the only time you'd want to return all rows is if you're using query results caching

[Warboss_Alex]

Really? Interesting. So there's no need to pull all the rows into memory, then? Sorted.

Is there some way to get the total number of rows without a count query, too?

[r937]

no

[Warboss_Alex]

What about FOUND_ROWS() ?

[swdev]

on a related note, is it faster / better to do a COUNT(*) type query or a COUNT(primary_key_field) type query?

I would have though the latter.

[r937]

FOUND_ROWS requires a query which uses LIMIT, and is based on the number of rows that would've been returned without the LIMIT, which is sematically different (and therefore potentially different in execution time) from a COUNT() query

COUNT(*) and COUNT(column) are different, the former ignores nulls, so the latter is usually slower because it actually has to scan the values in the column

however, if column is the primary key, it can't be null, so it's possible (but not guaranteed) that the optimizer is smart enough to realize this, and change it to a COUNT(*)

note that COUNT(*) can use any index on the table, but COUNT(column) can only use an index if there exists an index on that column, and if there does not, it has to scan the table

so COUNT(*) is usually faster, and never slower

[swdev]

Thanks for that r937.

Seems counter-intuitive to me when the column used is the primary key, but from now on I'll be using COUNT(*).

[r937]

yes, well, except if what you really need is COUNT(column), i.e. excluding nulls

[Warboss_Alex]

FOUND_ROWS requires a query which uses LIMIT, and is based on the number of rows that would've been returned without the LIMIT, which is sematically different (and therefore potentially different in execution time) from a COUNT() query

r937, according to what you say, then FOUND_ROWS is ideal for pagination, no? Say you're retrieving all the posts for a particular forum from a posts table, there might be 100 records for that particular forum, and you're showing them 20 at a time, you'd still be paginating (not displaying) 100 records, not 20. Did I get that right?

Alex ...

[r937]

yes