What is faster.....????

jonsof

i want to count all the rows of a table

1) "SELECT COUNT(*) FROM TABLE"

2) mysql_num_rows(SELECT * FROM TABLE)

what is faster?????

ednark

you tell me

<?php
$start = microtime();
### your code here
$end = microtime();
print "<br /><br />First Section took ". max($end,$start)-min($end,$start) ." seconds.";
?>

jonsof

And the results are.....???

ednark

you tell me

jonsof

i put the following code

<?

mysql_connect("localhost");
mysql_select_db("announcements_system");
$start = microtime();
$result=mysql_query("SELECT * FROM post");
$num=mysql_num_rows($result);
$end = microtime();
print "<br /><br />First Section took ". max($end,$start)-min($end,$start) ." seconds.";
echo "<br>";

$start = microtime();
$result=mysql_query("SELECT count(*) FROM post");
$row=mysql_fetch_assoc($result);
$end = microtime();
print "<br /><br />First Section took ". max($end,$start)-min($end,$start) ." seconds.";
?>

results :
-0.184668 seconds
-0.194973 seconds

-0.33486 seconds.
-0.342905 seconds.

-0.944245 seconds.
-0.149666 seconds.

-0.485084 seconds.
-0.495755 seconds.

I can't consinder any of these method faster than the other.
Most of times they are identicals.

Your opinion?

ednark

i would agree... about equal... mysql still has to do the logic of which rows get included into the resultant set, and thats the heavy part of the operation. so i don't see how any other function could leverage that much of a advantage over another method.... try averaging 10,000 or so runs and see how it turns out

pear.php.net has a Benchmark tool that you can use if you want to get into it more

jonsof

I am wondering which of these methods are used in forum like this! It has to manipulate huge amount of rows and tables and is fast, hawever.

localhost

i believe this should be faster than the above:

$numrows= @mysql_result(@mysql_query("SELECT COUNT(1) AS rows FROM table"), 0, "rows");

jonsof

Originally posted by localhost
i believe this should be faster than the above:
$numrows= @mysql_result(@mysql_query("SELECT COUNT(1) AS rows FROM table"), 0, "rows");
[/B]

COUNT(1)

1 what one means?

ednark

faster? perhaps not.... you'll have to test...

COUNT(1) will select the integer 1 as the result of each row... then count how many 1's it found...

i remember reading somewhere that you should actually do a COUNT(*) it seems like it would be slower in principle but whatever i read said that its actually faster because mysql is oprimized to look for it and cheat instead of actually grabbing all the rows... only a test will tell for sure

jonsof

i tested the 3 methods and the reasults are also identiacals to each other in table of 51 rows.

So do you think that COUNT(*) is the best method?

localhost

im sure mysql_num_rows is much slower

now thinks seem to be confused about how to use COUNT()

make sure u take a look at these links:

http://www.php.net/manual/en/function.mysql-num-rows.php

http://www.faqts.com/knowledge_base/view.phtml/aid/114/fid/12

http://www.php.net/manual/en/function.mysql-result.php

[greek]
geia sou jonsof twra katalava pws eisai ellhnas, kalh
douleia!
[/greek]

ednark

never believe what you read...

you can accept what you read if you don't have a method of testing it..

otherwise only believe what you test....

even text books are wrong sometimes

and testing something like this is trivial...

jonsof

I extracted the following

webmaster at _NOSPAM_elite-gaming dot com
10-Oct-2002 08:48

The fastest way to get the number of rows in a table is doing this:

$total = mysql_result(mysql_query("SELECT COUNT(id) FROM yourtable"),0);

As long as there are no NULL ids (shouldnt be), it will return the correct rows extremely fast. If you already used yourtable though, it is faster to use mysql_num_rows() on the result of it.

from http://www.php.net/manual/en/function.mysql-num-rows.php

This guy seems to has tested the particular method.

[greek]
Thanks localhost 😉. Poso kairo asxolise me PHP? [/greek]