Mysql SELECT COUNT(*) OR SELECT 1? PDO

Question

It has long been known that PDO does not support COUNT(*) and a query like below would fail as it doesn't return any affected rows,

$q = $dbc -> prepare("SELECT COUNT(*) FROM table WHERE id = ?");
$q -> execute(array($id));
echo $q -> rowCount();

Doing some research I found that you can also get the row count using other methods of count and not using count at all, for example the following query is supposed be the same as above but will return correct for PDO,

$q = $dbc -> prepare("SELECT 1 FROM table WHERE id = ?");
$q -> execute(array($id));
echo $q -> rowCount();

There are various sources on the internet claiming that;

"SELECT COUNT(*)
"SELECT COUNT(col)
"SELECT 1

Are all the same as each other (with a few differences) so how come using mysql which PDO cannot properly return a true count, does

"SELECT 1 

work?

Methods of count discussion

Why is Select 1 faster than Select count(*)?

Answer 1

PDO does not support COUNT(*)

WTF? Of course PDO supports COUNT(*), you are using it the wrong way.

$q = $dbc->prepare("SELECT COUNT(id) as records FROM table WHERE id = ?");
$q->execute(array($id));    
$records = (int) $q->fetch(PDO::FETCH_OBJ)->records;

If you are using a driver other than MySQL, you might have to test rowCount first, like this.

$records = (int) ($q->rowCount()) ? $q->fetch(PDO::FETCH_OBJ)->records : 0;

Answer 2

Oh. You are confusing everything.

1. PDO do not interfere with SQL queries. It support EVERYTHING supported by SQL.
2. When doing COUNT(*) you shouldn't use rowcount at all, as it just makes no sense. You have to retreive the query result instead.
3. Dunno what "various sources" you are talking about but COUNT(*) and COUNT(col) (and even COUNT(1)) are the same and the only proper way to get count of records when you need no records themselves.

COUNT is an aggregate function, it counts rows for you. So, it returns the result already, no more counting required. Ad it returns just a scalar value in the single row. Thus, using rowcount on this single row makes no sense

SELECT 1 is not the same as above, as it selects just literal 1 for the every row found in the table. So, it will return a thousand 1s if there is a thousands rows in your database. So, rowcount will give you the result but it is going to be an extreme waste of the server resources.

there is a simple rule to follow:

Always request the only data you need.

If you need the count of rows - request count of rows. Not a thousand of 1s to count them later.
Sounds sensible?