Paging Question

[NoamBarz]

I have a bunch of items stored in a table and I need to display them using paging, so that 10 items will be displayed on a page. In order to find out the number of pages I'll have, I need to find the total number of records and divide it by 10 (10 items per page). There are two options I can think of in order to do this.

1. find the number of records by using an sql cout function:
select count(id) from.... then do another select only for the items within my limit and display them.


2. select all of the items, only display those within my limit and use
mysql_num_rows or an equivalent to get the total number of records.

The 1st option uses 2 sql statements, however, it only selects the needed number of records. The 2nd option uses only 1 sql statement, but selects all of the records each time (without displaying all of them).

Is there another option I didn't think of? Which of the above two options will produce better results in terms of performance?

[ecentricNick]

I would do a select count(*) to get the total number of rows (which will run very fast as the database already knows how many rows there are in the table)

And then do a second select using the LIMIT <start>,<number> clause.

[convertor]

use LIMIT clause
select * from table_name LIMIT 0,10
this will select all columns in the table_name and returns first ten rows as a result