Here are some thoughts on the couple of methods shown: 1. SELECT count(*) as MyCount FROM aTable # Fast query done on server size SELECT * FROM aTable LIMIT 5 # Fast limited data return query done server side 2. SELECT * FROM aTable # Potentially slow query, lots of data stored in PHP mysql_num_rows() # Quick PHP-side function to tell how many results returned 3. SELECT SQL_CALC_FOUND_ROWS * FROM $table LIMIT 5 # Fast server side query that does the same as "SELECT count(*)" and "SELECT LIMIT 5" at the same time SELECT FOUND_ROWS() # Fast server side query Seems like #1 and #3 are your best options. Both are pretty easy on your system. #3 is definitely pretty slick, thanks to whoever originally posted that. But I'm wondering if it'll be fully compatible in the future. #1 is pretty "SQL 101" type stuff, so it should remain functional and be usable on other database platforms as well. That's the one I'd go for, just for the sake of keeping things simple if nothing else. Seems like a waste to do two database queries, but as long as you're not closing and re-opening the connection in between, it should cause very minimal impact on your system. Sometimes two queries are better than one. -TG = = = Original message = = = I see they call mysql_query twice which doesn't look like I'm gaining anything over running the 2 queries: SELECT count(*) as MyCount FROM aTable SELECT * FROM aTable LIMIT 5 But I suppose I need to time it for my app. http://us3.php.net/mysql_num_rows mancini at nextcode dot org 14-Nov-2005 02:24 here is a really fast mysql_num_rows alternative that makes use of the SELECT FOUND_ROWS() MySQL function , it only reads a single row and it is really helpfull if you are counting multiple tables with thousands of rows <?php function get_rows ($table) $temp = mysql_query("SELECT SQL_CALC_FOUND_ROWS * FROM $table LIMIT 1"); $result = mysql_query("SELECT FOUND_ROWS()"); $total = mysql_fetch_row($result); return $total[0]; ?> +++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ alex dot feinberg 4t gm41l 28-Apr-2005 04:56 Re dzver at abv dot bg's note... I just ran some tests using MySQL Super Smack. Surprisingly, a SELECT * followed by a SELECT COUNT(*) actually was close in speed to a SELECT SQL_CALC_FOUND_ROWS * followed by a SELECT FOUND_ROWS(), but the SQL_CALC_FOUND_ROWS solution was still a bit faster. Perhaps it varies by table structure? Either way, it might be worth checking which is faster for your application. Regards, Dwight > -----Original Message----- > From: Dwight Altman [mailto:dwight@xxxxxxxxxxxx] > Sent: Wednesday, July 05, 2006 9:53 AM > To: 'php-db@xxxxxxxxxxxxx' > Subject: RE: LIMIT > > So how do I get this information on the PHP side? > > mysql> SELECT SQL_CALC_FOUND_ROWS * FROM tbl_name > -> WHERE id > 100 LIMIT 10; > mysql> SELECT FOUND_ROWS(); > > I count 2 queries above (unless there is some caching magic). Whatever. > I was hoping to add another column (like FOUND_ROWS) to an existing query > and be able to pull out the FOUND_ROWS when I loop over the result set. > Something like: > SELECT *, FOUND_ROWS FROM aTable LIMIT 5 > > But since that single query doesn't work, how do I apply the MySQL > solution and extract it on the PHP side? > > http://dev.mysql.com/doc/refman/4.1/en/information-functions.html > http://www.mysqlfreaks.com/statements/101.php > > > Regards, > Dwight > > > -----Original Message----- > > From: Chris [mailto:listschris@xxxxxxxxxxxxxxx] > > Sent: Friday, June 30, 2006 9:46 AM > > To: php-db@xxxxxxxxxxxxx > > Subject: Re: LIMIT > > > > If you're using MySQL then: > > > > SELECT SQL_CALC_FOUND_ROWS * FROM aTable LIMIT 5 > > SELECT FOUND_ROWS() > > > > It's in the mysql documentation under SELECT syntax I believe. > > > > Chris > > > > Dwight Altman wrote: > > > Is there a way to get the number of rows that would have been returned > > had > > > there not been a LIMIT clause in a SELECT statement? > > > > > > For example, if > > > Query #1) SELECT * FROM aTable > > > would normally return 100 rows. But > > > Query #2) SELECT * FROM aTable LIMIT 5 > > > will return 5 rows. Is there a way to find out that 100 rows would > have > > > been returned if there was no LIMIT clause, by using only Query #2 and > > maybe > > > a PHP function on the $result? > > > > > > Regards, > > > Dwight > > > > > > > > > > > > > -- -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php ___________________________________________________________ Sent by ePrompter, the premier email notification software. Free download at http://www.ePrompter.com. -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
