php mysql performance pdo query-performance

Proper way to count the exact no. of rows in SQL using PHP PDO

I want to count the exact no. of rows in my table, this is how I'm doing. Let me know if I'm doing any blunder in terms of performance.

Old way

//I'm doing this to verify if the entered User name is correct.

$stmt = $conn->prepare("SELECT * FROM `users` WHERE `User` = ?");
$stmt->execute(array($user));
$Count = $stmt->rowCount();
echo $Count;

In some site, I read that the use of SELECT * has some performance issues so it should be avoided if all the columns are not required so I have changed to

$stmt = $conn->prepare("SELECT `id` FROM `users` WHERE `User` = ?");
$stmt->execute(array($user));
$Count = $stmt->rowCount();
echo $Count;

I thought of using MYSQL function COUNT() though I'm not sure if it will boost the performance in tables with few million rows and approximately 8-9 columns.

$stmt = $conn->prepare("SELECT COUNT(`id`) FROM `users` WHERE `User` = ?");
$stmt->execute(array($user));
for($stmt as $row)
    $Count = $row[0];
echo $Count;

Which one out of the above should I use or is there are some other better methods?

Solution

Using COUNT(*) is more efficient than fetching all rows and then count.

So you should use:

$stmt = $conn->prepare("SELECT COUNT(`id`) FROM `users` WHERE `User` = ?");
$stmt->execute(array($user));
$count = $stmt->fetchColumn();
echo $Count;