Methods to Count number of rows in a SELECT

[marf]

I know how to do this, but my question is I have come across 3 ways.

 

First is

 

SELECT count(*)

 

 

Second is

 

SQL_CALC_FOUND_ROWS

 

Third is

 

$result = mysql_query(...)

 

mysql_num_rows($result)

 

 

So what is the norm? What is best practice these days? I know that count(*) is the older method and I think its depreciated,  SQL_CALC_FOUND_ROWS is for version 4 and up I believe.

[Zane]

there isn't really a norm to it.

You just find the one that works best for you and stick with it.

 

I personally like to count them at query time...using COUNT(*) as whatever...then I can just access that variable later.

 

If you know you're gonna need that count a lot in your code I'd recommend using COUNT(*) to save code

if you just need only one time...then use mysql_num_rows()

[marf]

is any one method "faster" or more Efficient in the SQL?  like does a query with SQL_CALC_FOUND_ROWS execute faster than count(*) ?

[bubblegum.anarchy]

SELECT count(*) is fastest

[fenway]

SELECT count(*) is fastest

 

Not always... it really depends what you're trying to accomplish, and have the LIMIT & OFFSET are like.  Sure, it's easier just to write a single query and "cheat" with calc_rows, sometimes it's better and sometimes it's worse... depends on all sorts of things, group bys, having clauses, etc.  No hard and fast rule.