Page 1 of 2
Fastest method for Counting rows
Posted: Fri Jan 13, 2006 12:58 am
by anjanesh
Hi
Which would be faster ?
Code: Select all
$Total = @mysql_num_rows(@mysql_query("SELECT * FROM `table1`"));
OR
Code: Select all
$Res = @mysql_query("SELECT COUNT(*) FROM `table1`");
$Row = @mysql_fetch_row($Res);
$Total = $Row[0];
Thanks
Posted: Fri Jan 13, 2006 2:55 am
by JayBird
the second one
Why is it??
Posted: Fri Jan 13, 2006 4:27 am
by sp2hari
Just curious ..
why is the second one faster
Posted: Fri Jan 13, 2006 4:56 am
by JayBird
...becuase the first one returns ALL the rows...the second one doesn't
Not an issue on small results sets, but you'll soon notice a difference on larger tables.
If you actually needed all the rows in the script at some point, then it is better to use the first example.
Posted: Fri Jan 13, 2006 5:06 am
by Jenk
if you are only counting rows, then yes, the second is faster, but if you are also using the data, as well as counting, then the first will be quicker than running two queries.
Also, stop suppressing errors on all of your commands. Errors are your friend, fix them, by handling them appropriately.
Again Why
Posted: Fri Jan 13, 2006 5:07 am
by sp2hari
ya i got it
But
Code: Select all
If you actually needed all the rows in the script at some point, then it is better to use the first example.
Why is this so.???
Posted: Fri Jan 13, 2006 5:09 am
by Jenk
running two queries is less efficient than running one.. which is kind of obvious
However, running a large query, which will contain a lot of data, only to count just the number of rows, is not as efficent as "SELECT COUNT(*)" as that only returns one cell, not an entire table.
Posted: Fri Jan 13, 2006 6:07 am
by josh
phpmyadmin is your friend, run queries in phpmyadmin and watch the query run time, play around with different stuff to get a feel for how long queries take (complicated joins and subqueries are usually the longest). Also if you have access, use the "top" screen on linux to watch the mysql cpu usage as your queries run
Posted: Fri Jan 13, 2006 6:16 am
by anjanesh
I dont see query time in phpMyAdmin - using 2.7.0 pl2
BTW, among the COUNT queries,
would
be slower than
?
Posted: Fri Jan 13, 2006 6:18 am
by JayBird
No difference
Again How??
Posted: Fri Jan 13, 2006 6:56 am
by sp2hari
It seems i am just asking how and why in this post.
Again why is there no difference between
Code:
SELECT COUNT(*) FROM `table1`
be slower than
Code:
SELECT COUNT(`Id`) FROM `table1`
Isn't the first case selecting * which must take more time.
Re: Again How??
Posted: Fri Jan 13, 2006 7:32 am
by Chris Corbyn
sp2hari wrote:It seems i am just asking how and why in this post.
Again why is there no difference between
Code:
SELECT COUNT(*) FROM `table1`
be slower than
Code:
SELECT COUNT(`Id`) FROM `table1`
Isn't the first case selecting * which must take more time.
You get the query times if you run these queries in the MySQL client. Set a up a test table with over 10,000 rows of dummy data and try both. I haven't tested but I'm inclined to think that the 'select *' is quicker than 'select col' since it doesn't have to look for the specific column, either way it will be negligible.
It is other way
Posted: Fri Jan 13, 2006 7:43 am
by sp2hari
I did a small check now
My table has 2417 rows with 25 columns which i think is pretty large for checking
Select * took 0.05 seconds
select id,title took 0.03 seconds;
Select id took 0.02 seconds
So is this the rule
Larger the selection data longer the time

Hey wait
Posted: Fri Jan 13, 2006 7:46 am
by sp2hari
Hey i forgot one thing
Select count(*) and count(id) are taking almost the same time
I think i need a bigger database to differentiate or maybe as mentioned earlier by Pimptastic they take equal time
Posted: Fri Jan 13, 2006 8:04 am
by JayBird
Ive just run it on a DB with about half a million rows.....no time difference