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:


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?


-- 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

Reply via email to