RE: LIMIT

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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


[Index of Archives]     [PHP Home]     [PHP Users]     [Postgresql Discussion]     [Kernel Newbies]     [Postgresql]     [Yosemite News]

  Powered by Linux