On mán, 2006-12-11 at 17:01 +1100, Chris wrote: > Mark Kirkwood wrote: > > Chris wrote: > > > >> It's the same as doing a select count(*) type query using the same > >> clauses, but all in one query instead of two. > >> > >> It doesn't return any extra rows on top of the limit query so it's > >> better than using pg_numrows which runs the whole query and returns it > >> to php (in this example). > >> > >> > >> Their docs explain it: > >> > >> http://dev.mysql.com/doc/refman/4.1/en/information-functions.html > >> > >> See "FOUND_ROWS()" > >> > > > > Note that from the same page: > > > > "If you are using SELECT SQL_CALC_FOUND_ROWS, MySQL must calculate how > > many rows are in the full result set. However, this is faster than > > running the query again without LIMIT, because the result set need not > > be sent to the client." yes but not any faster than a select count(*) from (full query without LIMIT) so the only advantage to the SQL_CALC_FOUND_ROWS thingie is that instead of doing select count(*) from full-query select * from query-with-LIMIT which will do the query twice, but possibly with different optimisations, you would do a non-standard select SQL_CALC_FOUND_ROWS query-with-LIMIT select FOUND_ROWS() which will do one full query, without any LIMIT optimisation, but with the same number of round-trips, and same amount of data over the line. the only case where the second way may be more effective, is if no LIMIT optimisation can be made, and where the dataset is larger than file buffer space, so that there is no effect from caching. gnari
