For SQL row count, is it more efficient to use PDO rowCount or count(*) AS?

Question

On my script, I have about 15 SQL queries just for counting the number of rows and displaying them to the user.

What is the most efficient way?

Should I use:

$stmt=$cxn->prepare("SELECT id FROM items WHERE seller=?");
$stmt->execute(array($username));
echo $stmt->rowCount();

Or this:

$stmt=$cxn->prepare("SELECT count(*) as count FROM items WHERE seller=?");
$stmt->execute(array($username));   
while($row=$stmt->fetch(PDO::FETCH_ASSOC))
    echo $row['count'];

Thanks in advance.

Answer 1

The short answer is Count(*) will be faster.

However, that assumes your not using the data, if you are going to select the data, and you want a count, then use your first method.

("SELECT id FROM items WHERE seller=?");

If you have an index on the table, then that will return almost instantly.

Answer 2

The rowCount command can be used not only for SELECT queries but also for UPDATE,INSERT etc. So that's a benefit for that.

However according to the PDO documentation :

not guaranteed for all databases and should not be relied on for portable applications.

So in your case i'd suggest using count without worrying about preformence, though it will slightly faster.

Answer 3

It will be faster to use the MySQL row count. Less bandwidth is needed between PHP and the database.