3 篇博文 含有标签「索引失效」

Copyright © 2024 PawSQL

问题定义​

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.

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

Use explain to get the query plan:

Copyright © 2023 PawSQL

问题定义​

ORDER BY 子句中的所有表达式必须按统一的 ASC 或 DESC 方向排序，以便利用索引；如果ORDER BY 语句对多个不同条件使用不同方向的排序无法使用索引。

譬如在TPCH的lineitem的表上创建索引：

create index l_partkey_suppkey_idx on lineitem(l_partkey, l_suppkey);