Fastest way to count in sql

We all know that stars in a select statement are a terrible idea

select * from message ;
It could give unpredictable results over time with schema evolution and give unoptimized queries, so good practice is to select what you need !!

Good star in SQL
Well, only some starts are good. One particular star is a good one!

Count (*): Tell your database to count rows of tables as fast as possible! It is a bit counterintuitive, but let's examine it further.

sql
select count(id) from message ;
┌───────────┐
│ count(id) │
├───────────┤
│ 1091      │
└───────────┘
Run Time: real 0.001 user 0.000170 
As you see on timing, it is fast, but we have a quicker result possible with

libsql> select count(*) from message ;
┌──────────┐
│ count(*) │
├──────────┤
│ 1091     │
└──────────┘
Run Time: real 0.000 user 0.000093 
How is it possible?

Let's ask explain

libsql> explain query plan select count(*) from message ;
QUERY PLAN
`--SCAN message USING COVERING INDEX idx_message_conversation
As we can see, it uses a secondary index much smaller than a clustering index that keeps a row of data. So, if you have any secondary indexes, the majority of query planers will use it for a fast count.

So even if it is counter-intuitive not all stars are bed in SQL