Page 1 of 1

counting rows

Posted: Fri Feb 15, 2013 4:27 pm
by gotornot
Hi All

I have a DB with over 950,000 rows and i want to be able to count how many rows are in the table.

The only trouble is a mysql_num_rows statement takes ages i need something a lot quicker.

This is what im using crrently and it doesnt work. any help would be great.

Code: Select all

                <?php
                $qx = "select count(*) FROM products";
                //$qnx = mysql_query( "SELECT SQL_CALC_FOUND_ROWS `id` From `products`" );
                //$qnx = mysql_query($qx);
                //$qx = "SELECT * FROM products";
                $qxr = mysql_query($qx);
                $qxn = mysql_num_rows($qxr);
                echo $qxn;
                
                ?>

Re: counting rows

Posted: Fri Feb 15, 2013 4:41 pm
by requinix
A faster count, an accurate number, or less code to get that number. Pick any two.

Re: counting rows

Posted: Fri Feb 15, 2013 4:44 pm
by gotornot
a faster count would be helpfuil

Re: counting rows

Posted: Fri Feb 15, 2013 5:17 pm
by requinix
For a fast count with little code and without guaranteeing accuracy you can do a SHOW TABLE STATUS.

Code: Select all

SHOW TABLE STATUS LIKE 'products'
and in the returned table there'll be a column "Rows" with a number that's probably close to the correct number.

For a fast count with accuracy you'll need more code. Every time a product is added or removed from the table, retrieve a count from someplace, +1 or -1 it, then resave. Like a cache, so that the first time the number is pulled it's slow but every time after it's fast.


I vaguely recall something about different table engines (namely MyISAM and InnoDB) being faster or slower with counts.

Re: counting rows

Posted: Fri Feb 15, 2013 9:57 pm
by Benjamin
Is it InnoDB? If so it literally does have to count the records.

MyISAM can just give you the current total.

Maybe create a stored procedure to update the count whenever records are added/deleted..

Re: counting rows

Posted: Fri Feb 15, 2013 11:02 pm
by Christopher
The thing about mysql_num_rows() is it is the size of the result set. Have you tried just:

Code: Select all

$qx = "SELECT count(*) AS total_rows FROM products";
$qxr = mysql_query($qx);
$row = mysql_fetch_assoc($qxr);
echo $row['total_rows'];