CODEWITHSUNDEEP
phpmysqlodbc
Mawg
Mawg

Reputation: 40140

What's the most efficient way to count rows in a table?

Is it more efficient to use:

$sql = 'SELECT COUNT(*) AS count FROM users';
$odbcResult = OdbcExec($sql);
@odbc_fetch_row($odbcResult);
$count = @odbc_result($odbcResult, 'count');

or to use:

$sql = 'SELECT * FROM users';
$odbcResult = OdbcExec($sql);    
$count = odbc_num_rows($odbcResult);

Upvotes: 3

Views: 1963

Answers (4)

ksbytes
ksbytes

Reputation:

The first method is definitely faster. But COUNT(id) being faster than COUNT(*) - questionable. They usually are exactly the same, and there are cases when COUNT(id) is actually slower (see: http://www.mysqlperformanceblog.com/2007/04/10/count-vs-countcol/)

Upvotes: 2

rayman86
rayman86

Reputation: 1395

$sql = 'SELECT COUNT(id) AS count FROM users';
$odbcResult = OdbcExec($sql);
@odbc_fetch_row($odbcResult);
$count = @odbc_result($odbcResult, 'count');

or some other index field besides id. Its a little faster than COUNT(*), but the count method is the way to go. If you need to do something with the results, method 2 is faster, but only needing count this is the one you want.

-edit- Added keyword of index before field. True comment below, made the assumption that there was an id index column (should have an index somewhere at any rate)

Upvotes: 2

Gareve
Gareve

Reputation: 3391

Populate a table with 10^x elements with x>=6 and see the time that takes.

Upvotes: 2

Brian Roach
Brian Roach

Reputation: 76898

The former. The server is doing the work. It may already know the answer without counting,

The later requires all the rows to be returned to your program over the network (and into memory).

Upvotes: 9

Related Questions