Displaying the Number of Entries

Anon

this is a relatively simple question (i think). when i run the "SELECT name FROM users;" command in mysql, i get the results followed by "156 rows in set" is there any way to get that number to display on a page?

Any help is much appreciated.

brandonschnell

$results = mysql_db_query("database","SELECT count(name) FROM users");

$row = mysql_fetch_array($results);

echo "There are $row[0] users in the database.";

jariizumi

$result = mysql_query($myquery);
echo "There are ".mysql_num_rows($result)." rows in the query";

should also do the work.

http://www.php.net/mysql_num_rows

brandonschnell

mysql_num_rows() also works, but its not as fast as using the mysql count() function.

take a 100,000 row database and count the rows using mysql_num_rows() and mysql count(). for me the slow down was very noticeable.

the way i understand it (correct me if i'm wrong) is that mysql_query("select * from table") grabs every row and hands it php. basically the entire table is being copied from mysql database into php's memory. next, by using msysql_num_row() php has to count every row in the data then return the result.

now contrast that with how mysql count() works. mysql_query("select count(row) from table") mysql considers only one row and counts it using an index or tablescan or whatever the fastest method is. the results, jjust a couple of bytes, are returned to php.

Anon

I didn't know that, good advice. Did you run an actual benchmark on this to check the time? May be worth doing and posting under the mysql_num_row PHP.net page.

Cheers,

-- npoole

brandonschnell

i found out about this after testing a database with images then expanding it to about 100,000 rows and having a page take 10+ seconds to render.

create:
create table test (id int,data text);

populate:
for($i=0;$i<100000;$i++)
{
query($db,"insert into test set id = $i,data='test data'");
}

to test:
echo microtime(),"\n";
$big_results = mysql_db_query("db_prod","select * from test");
$big_row_num = mysql_num_rows($big_results);
echo microtime(),"\n";
$small_results = mysql_db_query("db_prod","select count(id) from test");
$small_row = mysql_fetch_array($small_results);
echo microtime(),"\n";

on my test machine the results are:
980497895.76506700 (script is starting)
980497896.42144000 (after mysql_num_rows)
980497896.52827700 (and after using count())

so it took 0.7 seconds for the mysql_num_rows method and a bit over 0.1 seconds for count(). while this may not sounds like much, try having images in your database (more to select) and try to access these pages on a system thats almost always under serious load. in my quick test i noticed that count() took about 0.1 seconds regardless of the amount of data but with just a couple of bytes more the mysql_num_rows method was noticeably slower.

1) there may be a better way to measure the time a script takes besides microtime() but i ran the script about 10 times and the results were about the same.

2) anyone else seeing this occur?

steved

To make sure you're getting the correct results, you should also run your test with the order of the two count methods reversed. That way you eliminate any advantage the second one might have due to memory caching.

Or better yet, run them multiple times, but only take the times from the second run.