Skip to main content

Counting Optimization

Copyright © 2023 PawSQL

DDL Definition​

-- tpch.nation definition
CREATE TABLE `nation` (
  `N_NATIONKEY` int NOT NULL,
  `N_NAME` char(25) NOT NULL,
  `N_REGIONKEY` int NOT NULL,
  `N_COMMENT` varchar(152) DEFAULT NULL,
  KEY `PAW_IDX1831397382` (`N_NATIONKEY`,`N_NAME`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

PostgreSQL​

  • c_name is not nullable
  explain analyze select count(c_name) from customer c ;
Aggregate  (cost=393.00..393.01 rows=1 width=8) (actual time=1.507..1.508 rows=1 loops=1)
  ->  Seq Scan on customer c  (cost=0.00..368.00 rows=10000 width=25) (actual time=0.008..0.514 rows=10000 loops=1)
explain analyze select count(1) from customer c ;
Aggregate  (cost=227.28..227.29 rows=1 width=8) (actual time=0.842..0.843 rows=1 loops=1)
  ->  Index Only Scan using c_nationkey_idx on customer c  (cost=0.29..202.28 rows=10000 width=0) (actual time=0.015..0.493 rows=10000 loops=1)
explain analyze select count(c_custkey) from customer c ;
Aggregate  (cost=299.29..299.30 rows=1 width=8) (actual time=0.871..0.872 rows=1 loops=1)
  ->  Index Only Scan using customer_pkey on customer c  (cost=0.29..274.29 rows=10000 width=4) (actual time=0.014..0.540 rows=10000 loops=1)

MySQL​

explain analyze select count(c_name) from tpch.customer c ;
-> Count rows in c  (actual time=0.029..0.029 rows=1 loops=1)
explain analyze select count(1) from tpch.customer c ;
-> Count rows in c  (actual time=0.029..0.029 rows=1 loops=1)

About PawSQL​

PawSQL is dedicated to automatic and intelligent database performance optimization. The products provided by PawSQL include:

  • PawSQL Cloud, an online automated SQL optimization tool that supports SQL auditing, intelligent query rewriting, cost-based index recommendations, suitable for database administrators and data application developers.
  • PawSQL Advisor, an IntelliJ plugin that is suitable for data application developers and can be installed via the IDEA/DataGrip marketplace by searching for "PawSQL Advisor" by name.
  • PawSQL Engine, which is the backend optimization engine of the PawSQL series of products, can be installed and deployed independently, and provides SQL optimization services through http/json interfaces. PawSQL Engine is provided for deployment and installation as a docker image.

Contact Us​

Email: service@pawsql.com

Twitter: https://twitter.com/pawsql