Questions tagged [covering-index]

The tag has no usage guidance.

Filter by
Sorted by
Tagged with
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 ( ...
Andrey B. Panfilov's user avatar
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 ...
userfuser's user avatar
  • 149
6 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 ...
tuk's user avatar
  • 1,193
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, ...
Nuno's user avatar
  • 809
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 ...
Heta Desai's user avatar
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, ...
Evan Barke's user avatar
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) ...
tomka's user avatar
  • 927
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 ...
DamagedGoods's user avatar
  • 2,591
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 ...
user9516827's user avatar
  • 1,305
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 ...
user9516827's user avatar
  • 1,305
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 ...
Tom's user avatar
  • 205
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" : ...
Kay's user avatar
  • 211
-2 votes
2 answers

Can a non-clustered index be transformed to a covering index in SQL Server?

I have a non-clustered index which includes one column. Now I want to alter the index and add additional two columns as covering columns. Is it possible? If yes, then can you, please, provide the T-...
igelr's user avatar
  • 2,120