counting rows

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
gotornot
Forum Commoner
Posts: 54
Joined: Fri Jul 31, 2009 2:30 am

counting rows

Post 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;
                
                ?>
User avatar
requinix
Spammer :|
Posts: 6617
Joined: Wed Oct 15, 2008 2:35 am
Location: WA, USA

Re: counting rows

Post by requinix »

A faster count, an accurate number, or less code to get that number. Pick any two.
gotornot
Forum Commoner
Posts: 54
Joined: Fri Jul 31, 2009 2:30 am

Re: counting rows

Post by gotornot »

a faster count would be helpfuil
User avatar
requinix
Spammer :|
Posts: 6617
Joined: Wed Oct 15, 2008 2:35 am
Location: WA, USA

Re: counting rows

Post 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.
User avatar
Benjamin
Site Administrator
Posts: 6935
Joined: Sun May 19, 2002 10:24 pm

Re: counting rows

Post 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..
User avatar
Christopher
Site Administrator
Posts: 13596
Joined: Wed Aug 25, 2004 7:54 pm
Location: New York, NY, US

Re: counting rows

Post 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'];
(#10850)
Post Reply