Pagination Best Practices

[tmk]

I'm not that deep into the internals of MySQL... so please excuse my ignorance when it comes to efficiency.

Let's say I have a (MySQL) database of several thousand records that is updated/added to quite frequently . I want to show, 'pagerows' records at a time with several SQL WHERE conditions. The WHERE conditions may return 0 to all records, depending on the critera.

All I really need are a the pagerows rows of data,  and links for Previous, Page-x-of-y , and Next

Which might be the more efficient:

1) Run a SELECT COUNT(*) to get total # of matching records, then run the real SELECT query with the fields and data I want with a LIMIT clause (LIMIT start,pagerows)

2) Run the SELECT query with the fields and data I want using the  and perform a while loop : While ($row=$result->FetchRow() {...}) fetching the start through start+pagerows.

3) Your suggestions

Thanks

[calguy1000]

Pick option 1

two queries:
  a) get the total count of matching records
      this allows you to determine page count, etc.
  b) get the matching records for the current page using a limit parameter.

that's the way I do it in all of the modules I write that require pagination.

The other option will give you nothing but problems with php memory requirements and performance.

[tmk]

Calguy, many thanks...

I was thinking #1, but was looking for confirmation, and I value your judgement.