There are many situations in which pagination of query results is very useful, especially for performance optimization.  In most of these kinds of situations, the paginating of results requires you to determine the total number of results, so the application knows the number of pages available.

The most common way to do this, is to use two queries; one which obtains the count of results and one which obtains a particular page of results.  This method has the potential to be even less optimal than loading the entire set of results, however; due to the fact that two queries are now necessary, whereas before there was only one query.

In most database systems, it is possible to overcome this limitation; though the technique is specific to the particular database you are using.  This example explains how to do this in MySQL.

Here is the sub-optimal example, using two queries; it is loading the first page of results. The LIMIT 0, 40 means it will start at position 0 (the beginning) and obtain a set of 40 results.

SELECT count(*) FROM my_table WHERE timestamp >= '2010-03-15' and timestamp <= '2010-08-01';

SELECT id, timestamp, field1, field2, field3 FROM my_table WHERE timestamp >= '2010-03-15' and timestamp <= '2010-08-01' ORDER BY id LIMIT 0, 40;

Here is a more optimal example, which uses two statements, only one of which is a real query.  Everything is done during the first statement, the second statement merely loads the count, which was calculated during the first statement.

SELECT SQL_CALC_FOUND_ROWS id, timestamp, field1, field2, field3 FROM my_table WHERE timestamp >= '2010-03-15' and timestamp <= '2010-08-01' ORDER BY id LIMIT 0, 40;

SELECT found_rows() AS cnt;

One of the drawbacks of SQL_CALC_FOUND_ROWS, or count(*) in general; is the fact that, by running these calculations, you lose some of the benefit of pagination. This is because your database is required to examine all of the effected data in order to generate an accurate count.

Depending on the specifics of your my.cnf configuration file, the first statement will cache part of the information, causing it to execute faster when subsequent pages are loaded. In some of my own testing I have seen a significant speedup after the first page is loaded.

If you want to get the most out of your application, you will likely need to do a combination of application tuning, query tuning and database tuning. Generally you will want to start by tuning the application itself; this way you're eliminating any bottlenecks inherent in the application. Then you'll need to do some query optimizations if the application still isn't fast enough. Finally, you'll want to look into what configuration changes you can make on the database in order to speed things up from the source.