Database Administrators Stack Exchange
Database Administrators Stack Exchange

Questions tagged [covering-index]

16 questions
8
votes
2 answers

Covering index used despite missing column

I have the following query, using MariaDB 10 / InnoDB: SELECT id, sender_id, receiver_id, thread_id, date_created, content FROM user_message WHERE thread_id = 12345 AND placeholder = FALSE ORDER BY date_created DESC LIMIT 20 This query…
Tom
  • 205
  • 1
  • 5
7
votes
3 answers

Advantage of using INCLUDE as against adding the column in INDEX for covering index

Postgres docs state the following about Index-Only Scans and Covering-Indexes: if you commonly run queries like SELECT y FROM tab WHERE x = 'key'; the traditional approach to speeding up such queries would be to create an index on x only. However,…
tuk
  • 1,263
  • 1
  • 15
  • 22
5
votes
4 answers

MySQL using a multi-column index even when the first column isn't being queried

I have MySQL version 8.0.37. From what I understand about a multi-column index in this version, it will be used by MySQL ONLY if the query contains a subset of all the columns, starting from the first. For ex, I have this index in my InnoDB…
Sidharth Samant
  • 203
  • 1
  • 6
2
votes
2 answers

Any pitfalls/benefits of creating PK with include(..) in PostgreSQL?

Application is aggressively caching data in memory and in order to support consistency (preventing persisting stale data) it is doing something like: -- typical table structure: create table t1 ( id varchar(16) primary key, …
Andrey B. Panfilov
  • 231
  • 1
  • 8
2
votes
2 answers

Non-clustered Primary Key and Clustered Index

It is my understanding that in SQL Server, you can have a Primary Key that is non-clustered, and have another index that is the clustered one. To me, this seems the same as just having a Primary Key, and an extra UNIQUE key. So I have two…
Nuno
  • 829
  • 1
  • 12
  • 24
2
votes
1 answer

Key lookup still happening after creating covering index

I've implemented a covering index to avoid a key lookup: CREATE INDEX IX_StatusHistory_Covering ON StatusHistory(ID) INCLUDE (Status_ID, StatusComment, StatusReason_ID, StatusReasonComment, UserEnteredStatusDateTime, …
Evan Barke
  • 33
  • 3
1
vote
0 answers

How to create Non-clustered Covering index on PK while creating a table

I am trying to create a table where PK will not be a clustered index. PK will be non clustered with INCLUDE clause to add cover. CREATE TABLE [Ref].[User]( [UserRegisteredId] [varchar](100) NOT NULL, [UserName] [varchar](500) NOT NULL, …
Heta Desai
  • 21
  • 2
1
vote
1 answer

Postgres 11+: are covering indices (INCLUDE) useful for join/where conditions?

I'd like to better understand when covering indices can be useful to make index-only scans possible in Postgres 11+. As the documentation says, given the covering index CREATE INDEX tab_x_y ON tab(x) INCLUDE (y); queries like this can use it for…
tomka
  • 967
  • 1
  • 10
  • 16
1
vote
1 answer

Indexing Strategy for the query performance

I have a question on indexing strategy as i am trying to do indexing for a database. I understand the basic indexing strategy which are: Clustered index on PrimaryKey Add Non Clustered index for other columns as per requirement. Should have indexes…
user9516827
  • 1,345
  • 3
  • 19
  • 41
0
votes
1 answer

MySQL: How to optimize a JOIN on a primary key with Indexes

I am looking to optimize a JOIN between tables that uses a small subset of columns in the second table. CREATE TABLE `employees` ( `employee_id` bigint NOT NULL, `manager_id` bigint NOT NULL, `org_id` bigint NOT NULL, `union_id` bigint NOT…
kingrich123
  • 1
0
votes
2 answers

How to have a covering index used for the updates?

I have a db table with" id bigint NOT NULL AUTO_INCREMENT (primary key) customer_ref varchar(64) NOT NULL customer_counter int NOT NULL description varchar(255) NOT NULL I have also the following indexes: PRIMARY KEY(id) UNIQUE KEY c_ref…
Jim
  • 123
  • 5
0
votes
1 answer

Adding a non-clustered, covering index, on a primary key, when clustered index exists

I went through the related articles, suggested by SO, but didn't really find all the answers, so I'll try to be specific here. Presume we have a (MSSQL) table, with a simple ID (int) primary key, with a clustered index only on it. Let's presume that…
userfuser
  • 147
  • 5
0
votes
2 answers

non clustered index coverage with INCLUDED columns

If I have two indexes: IDX_1 = (COL1), (COL2) DESC IDX_2 = (COL1) INCLUDE (COL3) will a single defined below cover both queries? does the Descending keyword have any bearing on the usage of INCLUDE statement for covering both queries? IDX_3 =…
DamagedGoods
  • 2,591
  • 6
  • 34
  • 48
0
votes
1 answer

Column as an Included column or as a key column

I have a query which has an optional where condition on column classID(not unique). This same classID is being displayed as a select column in various other queries. Currently i have classID as an included column in an index idx,so i think that the…
user9516827
  • 1,345
  • 3
  • 19
  • 41
0
votes
1 answer

Why a query is not covered when using a range operator on documents?

I have created the following 1000 documents: for(i=0;i<1000;i++){db.doc.insert({"doc":{"k1":"v_"+i,"k2":i},"scal":i})} So a document looks like this: var d1 = db.doc.findOne() { "_id" : ObjectId("5ce25f8920b0be2428648e38"), "doc" : { …
Kay
  • 211
  • 1
  • 7
1
2