Re: count() total records for pagination with limit

Chris wrote: > PJ wrote: >> I seem to recall that it is possible to count all instances of a query >> that is limited by $RecordsPerPage without repeating the same query. I >> believe that COUNT() had to called immediately after the SELECT word but >> I neglected to bookmark the source. Dummy! > > You're probably thinking of > > SQL_CALC_FOUND_ROWS > > (http://dev.mysql.com/doc/refman/5.0/en/limit-optimization.html) > > It's not always faster though > > (http://www.mysqlperformanceblog.com/2007/08/28/to-sql_calc_found_rows-or-not-to-sql_calc_found_rows/). > > >> I don't like the idea of count(*) over count() or something like that as >> it seems rather slow fram what I read. >> right now I do this: >> $sql = "SELECT * FROM book >> WHERE id IN (SELECT bookID >> FROM book_author WHERE authID IN (SELECT author.id >> FROM author WHERE LEFT(last_name, 1 ) = '$Auth')) "; >> $Count1 = mysql_num_rows(mysql_query($sql, $db)); > > The problem with this is if your '*' includes 50 fields (from all of > the tables in the joins etc) then that is still processed in mysql > taking up memory especially. > > Doing a count() just has 1 field - the count. > > $sql = "select count(1) as count from book .... > "; > $result = mysql_query($sql, $db); > $row = mysql_fetch_assoc($result); > $count = $row['count']; > >> $Count gives me the actual rows for display - $Count1 gives me the total >> rows available. >> >> Can this be streamlined any? > > Not really. > OK. Your suggestion does help, though. :-) -- unheralded genius: "A clean desk is the sign of a dull mind. " ------------------------------------------------------------- Phil Jourdan --- pj@ptahhotep.com http://www.ptahhotep.com http://www.chiccantine.com/andypantry.php