Geek asks and answers

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

How much more efficient to use:

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

or use:

$sql = 'SELECT * FROM users';
$odbcResult = OdbcExec($sql);    
$count = odbc_num_rows($odbcResult);
+3
source share
4 answers

The first. The server is doing the work. He may already know the answer without regard,

Later it is required that all lines be returned to your program over the network (and into memory).

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

or an index field other than id. Its a little faster than COUNT (*), but the counting method is the way to go. If you need to do something with the results, method 2 is faster, but you just need to assume that this is the one you want.

-edit- . , , ( - )

+2

We fill in the table with elements 10 ^ x with x> = 6 and see the time it takes.

+2
source

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

+2
source

More articles: