PHP & PDO: Which is faster? Fetching COUNT(*) or rowCount()?

Question

OK, my problem is that I have a script that loads on every page, it checks if a signed in user is banned or not by checking a column named "banned" that can output either 0 (not banned) or 1 (banned). I've tried searching for this, but didn't find an absolute answer to which performs better:

Fetching a COUNT(*) query:

$query = $PDO->prepare("SELECT COUNT(*) FROM users WHERE id = :ID AND banned = 1");
$query->execute(array(":ID" => $USER_ID));
if ($query->fetchColumn() > 0) {
    // USER IS BANNED! SHOW ERROR MESSAGE!
}

Using rowCount() instead of fetching:

$query = $PDO->prepare("SELECT banned FROM users WHERE id = :ID AND banned = 1");
$query->execute(array(":ID" => $USER_ID));
if ($query->rowCount() > 0) {
    // USER IS BANNED! SHOW ERROR MESSAGE!
}

Test it! Echo the time difference between start time and stop time. Do this many times. — AbraCadaver, May 21 '18 at 14:59
Could add `limit 1` to restrict the second one from looking for more rows. — Nigel Ren, May 21 '18 at 15:01
Could also `SELECT 1` in the second case ... but I strongly suspect we're talking about such tiny differences in performance it's not really worth bothering with unless you're sort of Google/Facebook kinda size. — CD001, May 21 '18 at 15:02
Possible duplicate of [Best way to test if a row exists in a MySQL table](https://stackoverflow.com/questions/1676551/best-way-to-test-if-a-row-exists-in-a-mysql-table) — iainn, May 21 '18 at 15:05
Question depens on used table engine, used indexes and table size.. But for counting the `SELECT COUNT(*) ...` would make the most sense and would perform the best if indexes are being used — Raymond Nijland, May 21 '18 at 15:14
According to [the manual](http://php.net/manual/en/pdostatement.rowcount.php) *PDOStatement::rowCount() returns the number of rows affected by the last DELETE, INSERT, or UPDATE statement executed by the corresponding PDOStatement object.* Therefore `rowCount` is not a replacement for `COUNT(*)` — apokryfos, May 21 '18 at 15:36

score 1 · Accepted Answer · answered May 21 '18 at 15:31

1

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.

answered May 21 '18 at 15:31

Mike

171
1
9

Have you tried the code from the duplicate flagged? Would be interesting if you've already got something setup. – Nigel Ren May 21 '18 at 15:33
`rowCount` is not a reliable means to count rows fetched by a select – apokryfos May 21 '18 at 15:37
`SELECT EXISTS` is up to 30% slower than either `rowcount()` or `COUNT(*)` – Mike May 21 '18 at 16:04

score 1 · Answer 2 · answered May 21 '18 at 15:31

select COUNT(*) is different from select *.
fetchColumn() and rowCount() both are excuted by mysql

(1) if data is too much in mysql, it will effect performs

(2) another , you can think they are same and you can use both of them
if you choose the first method, mysql will return you all of the data result, it will use data transform, net, time, etc

so you should consider about your real enviroment to decide use which one.

PHP & PDO: Which is faster? Fetching COUNT(*) or rowCount()?

2 Answers2