PDO::rowCount VS COUNT(*)
1st question:
Using count COUNT()
, internally the server(MySQL) will process the request differently.
When doing COUNT()
, the server(MySQL) will only allocate memory to store the result of the count.
When using $row=$SQL->rowCount();
the server (Apache/PHP) will process the entire result set, allocate memory for all those results, and put the server in fetching mode, which involves a lot of different details, such as locking.
Take note that PDOStatement::rowCount()
returns the number of rows affected by the last statement, not the number of rows returned. If the last SQL statement executed by the associated PDOStatement
was a SELECT
statement, some databases may return the number of rows returned by that statement. However, this behaviour is not guaranteed for all databases and should not be relied on for portable applications.
On my analysis, if you use COUNT()
, the process would be divided to both MySQL and PHP while if you use $row=$SQL->rowCount();
, the processing would be more for PHP.
Therefore COUNT()
in MySQL is faster.
2nd question:
COUNT(*)
is better than COUNT(id)
.
Explanation:
The count(*)
function in mysql is optimized to find the count of values. Using wildcard means it does not fetch every row. It only find the count. So use count(*)
wherever possible.
Sources:
- PDOStatement::rowCount
- MySQL COUNT(*)
For SQL row count, is it more efficient to use PDO rowCount or count(*) AS?
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.
PDO fetch vs rowcount
got it sorted. solution i used was:
# if data is valid, check against values held in the database and then action as appropriate
if ($valid) {
$sql = 'SELECT id, email, password FROM author
WHERE email = :email AND password = :password';
$s = $dbConnection->prepare($sql);
$s->bindValue(':email', $email);
$s->bindValue(':password', $password);
$s->execute();
$data = $s->fetch();
if($data != NULL){
$_SESSION['loggedIn'] = TRUE;
$_SESSION['email'] = $email;
$_SESSION['id'] = $data['id'];
header('Location:../admin/');
exit;
}else{
unset($_SESSION['loggedIn']);
unset($_SESSION['email']);
unset($_SESSION['id']);
echo 'Incorrect username/password combination';
}
}
Row count with PDO
$sql = "SELECT count(*) FROM `table` WHERE foo = ?";
$result = $con->prepare($sql);
$result->execute([$bar]);
$number_of_rows = $result->fetchColumn();
Not the most elegant way to do it, plus it involves an extra query.
PDO has PDOStatement::rowCount()
, which apparently does not work in MySql. What a pain.
From the PDO Doc:
For most databases,
PDOStatement::rowCount() does not
return the number of rows affected by
a SELECT statement. Instead, use
PDO::query() to issue a SELECT
COUNT(*) statement with the same
predicates as your intended SELECT
statement, then use
PDOStatement::fetchColumn() to
retrieve the number of rows that will
be returned. Your application can then
perform the correct action.
EDIT: The above code example uses a prepared statement, which is in many cases is probably unnecessary for the purpose of counting rows, so:
$nRows = $pdo->query('select count(*) from blah')->fetchColumn();
echo $nRows;
PHP & PDO: Which is faster? Fetching COUNT(*) or rowCount()?
I performed a benchmark with 200k randomly generated entries with three columns id, name, and banned. 3467 users were randomly selected to be banned. I am using the following configuration on an AWS micro instance:
PHP 7.0.30-0
Ubuntu 0.16.04.1
MySQL 5.7.22-0
Benchmark results:
COUNT(*) execution took 0.00022506713867188 seconds
rowcount() execution took 0.00011420249938965 seconds
rowcount()
is the winner.
How do I count unique rows in php pdo?
You can use distinct in mysql to select only unique fields in your table.
$query = "SELECT distinct user_id FROM users";
$stmt = $db->prepare($query);
$stmt->execute();
$count = $stmt->rowCount();
Related Topics
How to Detect Non-Ascii Characters in a String
Reaching 100% Code Coverage with PHPunit
How Do Check If a PHP Session Is Empty
Check If Laravel Model Got Saved or Query Got Executed
Best Way to Autoload Classes in PHP
Group Array Values Based on Key in PHP
How to Get Values Inside <![Cdata[Values]] > Using PHP Dom
How to Check If Directory Contents Has Changed with PHP
PHP Remove Elements from Associative Array
Removing Black Borders 4:3 on Youtube Thumbnails
Url Encode Equivalent in Ruby on Rails
How to Write to Error Log File in PHP
How to Check for a Specific Type of Object in PHP
Getting List Ips from Cidr Notation in PHP
How to Replace Multiple Items from a Text String in PHP