DEV Community

Volodymyr Pavlyshyn
Volodymyr Pavlyshyn

Posted on

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


Enter fullscreen mode Exit fullscreen mode

Do your career a big favor. Join DEV. (The website you're on right now)

It takes one minute, it's free, and is worth it for your career.

Get started

Community matters

Top comments (0)

Billboard image

Try REST API Generation for MS SQL Server.

DevOps for Private APIs. With DreamFactory API Generation, you get:

  • Auto-generated live APIs mapped from database schema
  • Interactive Swagger API documentation
  • Scripting engine to customize your API
  • Built-in role-based access control

Learn more