Questions tagged [covering-index]
The covering-index tag has no usage guidance.
13
questions
2
votes
2
answers
277
views
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 (
...
0
votes
1
answer
282
views
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 ...
- 149
6
votes
3
answers
4k
views
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 ...
- 1,193
2
votes
2
answers
1k
views
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, ...
- 809
1
vote
0
answers
30
views
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 ...
- 21
2
votes
1
answer
179
views
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,
...
- 33
1
vote
1
answer
184
views
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) ...
- 927
0
votes
2
answers
73
views
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 ...
- 2,591
1
vote
1
answer
141
views
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 ...
- 1,305
0
votes
1
answer
615
views
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 ...
- 1,305
8
votes
2
answers
373
views
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 ...
- 205
0
votes
1
answer
82
views
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" : ...
- 211
-2
votes
2
answers
111
views
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-...
- 2,120