This content originally appeared on DEV Community and was authored by Volodymyr Pavlyshyn
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
This content originally appeared on DEV Community and was authored by Volodymyr Pavlyshyn
Volodymyr Pavlyshyn | Sciencx (2024-09-11T15:47:46+00:00) Fastest way to count in sql. Retrieved from https://www.scien.cx/2024/09/11/fastest-way-to-count-in-sql/
Please log in to upload a file.
There are no updates yet.
Click the Upload button above to add an update.