I think the first item should be COUNT(*), because as you mentioned it
would be pretty inefficient to pull all the data just to get a count. In
MySQL 4 you can move step 4 to step 1 with a slight modification and
leave it at three steps. Lookup the FOUND_ROWS() function to find out
all the details. This feature was not available in v3 so you needed to
do two queries.
I actually just did what you are trying to do.
On Friday, February 14, 2003, at 03:59 PM, David Chamberlin wrote:
Hey,
One more on efficiency. Basically all the examples I've seen for doing
'windowed dbase queries' (i.e., displaying a limited set of results and
providing next/prev paging capabilities) have the same basic format:
1.SELECT * FROM table
2. get the number of rows from result
3. figure out paging scheme
4. SELECT desired data FROM table LIMIT startPoint,numEntries
It seems to me this is annoyingly inefficient forcing every page to
query for the entire list first, then re-running the query for the
desired subset. I guess there's no way around that, right?
My real question is, is there any particular reason (other than
laziness) that the first query (which fetches all results) is done with
a '*'. It seems to me that especially if you have a lot of data in
your table, that's unnecessarily inefficient since it's got to retrieve
and store in memory all the fields. Since you only really need to know
how many rows there are, wouldn't it be much more efficient to just
pick one particular column, and make that column the smallest one
available?
-Dave
-- PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
--
Brent Baisley
Systems Architect
Landover Associates, Inc.
Search Advisory Services for Advanced Technology Environments
p: 212.759.6400/800.759.0577
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
