Fastest method for Counting rows

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

User avatar
anjanesh
DevNet Resident
Posts: 1679
Joined: Sat Dec 06, 2003 9:52 pm
Location: Mumbai, India

Fastest method for Counting rows

Post 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
User avatar
JayBird
Admin
Posts: 4524
Joined: Wed Aug 13, 2003 7:02 am
Location: York, UK
Contact:

Post by JayBird »

the second one
User avatar
sp2hari
Forum Newbie
Posts: 14
Joined: Fri Jan 13, 2006 3:54 am

Why is it??

Post by sp2hari »

Just curious ..
why is the second one faster
User avatar
JayBird
Admin
Posts: 4524
Joined: Wed Aug 13, 2003 7:02 am
Location: York, UK
Contact:

Post 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.
User avatar
Jenk
DevNet Master
Posts: 3587
Joined: Mon Sep 19, 2005 6:24 am
Location: London

Post 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.
Last edited by Jenk on Fri Jan 13, 2006 5:08 am, edited 1 time in total.
User avatar
sp2hari
Forum Newbie
Posts: 14
Joined: Fri Jan 13, 2006 3:54 am

Again Why

Post 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.???
User avatar
Jenk
DevNet Master
Posts: 3587
Joined: Mon Sep 19, 2005 6:24 am
Location: London

Post 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.
josh
DevNet Master
Posts: 4872
Joined: Wed Feb 11, 2004 3:23 pm
Location: Palm beach, Florida

Post 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
User avatar
anjanesh
DevNet Resident
Posts: 1679
Joined: Sat Dec 06, 2003 9:52 pm
Location: Mumbai, India

Post by anjanesh »

I dont see query time in phpMyAdmin - using 2.7.0 pl2
BTW, among the COUNT queries,
would

Code: Select all

SELECT COUNT(*) FROM `table1`
be slower than

Code: Select all

SELECT COUNT(`Id`) FROM `table1`
?
User avatar
JayBird
Admin
Posts: 4524
Joined: Wed Aug 13, 2003 7:02 am
Location: York, UK
Contact:

Post by JayBird »

No difference
User avatar
sp2hari
Forum Newbie
Posts: 14
Joined: Fri Jan 13, 2006 3:54 am

Again How??

Post by sp2hari »

It seems i am just asking how and why in this post. :cry:

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.
User avatar
Chris Corbyn
Breakbeat Nuttzer
Posts: 13098
Joined: Wed Mar 24, 2004 7:57 am
Location: Melbourne, Australia

Re: Again How??

Post by Chris Corbyn »

sp2hari wrote:It seems i am just asking how and why in this post. :cry:

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.
User avatar
sp2hari
Forum Newbie
Posts: 14
Joined: Fri Jan 13, 2006 3:54 am

It is other way

Post 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 :lol:
User avatar
sp2hari
Forum Newbie
Posts: 14
Joined: Fri Jan 13, 2006 3:54 am

Hey wait

Post 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
User avatar
JayBird
Admin
Posts: 4524
Joined: Wed Aug 13, 2003 7:02 am
Location: York, UK
Contact:

Post by JayBird »

Ive just run it on a DB with about half a million rows.....no time difference
Post Reply