13 posts tagged with "rewrite"

Copyright © 2024 PawSQL

Problem​

Let's say we have a b-tree index c_acctbal_idx defined on table tpch.customer to accelarate queries with a condition on column c_acctbal. Let's check a query as follows:

select * from tpch.customer where c_acctbal + 100 = 10000.0

Copyright © 2024 PawSQL

Problem​

Let's say the O_ORDERDATE column in TPCH.ORDERS table is defined as VARCHAR(16), and there is an index ORDDATE_IDX on ORDERS(O_ORDERDATE) to accelerate the queries against this table if there is a condition on O_ORDERDATE. If we want to count the number of orders of today using following SQL query.

select count(*) from ORDERS 
    where O_ORDERDATE = current_date();

Use explain to get the query plan:

-> Filter: (orders.O_ORDERDATE = <cache>(curdate()))  (cost=2621.05 rows=19938)
    -> Table scan on ORDERS  (cost=2621.05 rows=199378)

From the query plan, we can see the database optimizer uses a table scan instead of the index lookup as we expected. The query cost is high.

Copyright © 2024 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;