Database covering a query

Question

Trying to understand what "covering a query means" with a specific example If I have a table with say 3 columns:

Col1 Col2 Col3

And I put an index on Col1 and Col2

is "covering a query" determine by the columns selected in the SELECT or the columns in the the WHERE Clause? Thus :

1) select Col1, Col2 from MyTable where Col3=XXX 
2) Select Col3 from MyTable where Col1=xxx and Col2=yyy
3) Select Col1, Col2 from MyTable where Col1=xxx and Col2=yyy

Which of these three are truly "Covered"?

Only query 3. Query 2 is partly covered - would be with `(col1,col2,col3)` index. Query 1 is not covered at ll, it would be with a `(col3,col1,col2)` index. — ypercubeᵀᴹ, Oct 01 '13 at 13:38
@Stijn i did see that link! The first answer confused me. This question has been troubling me - ive asked it specifically with these three queries in mind to get this completely squared in my head. A simple answer with just the numbers in the above would help me 100% — user2796381, Oct 01 '13 at 13:39
@ypercube is it the WHERE Clause that drives "covering" of a query? or both the columns in the SELECT clause and the columns in the WHERE clause? — user2796381, Oct 01 '13 at 13:41
Both. To claim that an index "covers" a query, the query must be able to use the index (and only that) to return results, without need of lookups on the table itself. — ypercubeᵀᴹ, Oct 01 '13 at 13:44
@ypercube thanks! Thats precisely what I wanted to know Cobwebs cleared - unless someone adds a quirk :) Please add your answer to Answers so I can answer this question — user2796381, Oct 01 '13 at 13:52
Yes, @Joel added the `INCLUDE ()` feature that I wanted to write. — ypercubeᵀᴹ, Oct 01 '13 at 14:43

Joel Coehoorn · Accepted Answer · 2013-10-02T20:31:50.993

Only the third example is covered. To be covered, a query must be fully satisfied from the index. Your first example produces results that are entirely within the index, but it needs information that is not part of the index to complete, and so is not covered. To match your first example, you need an index that lists Col3 first.

One important feature of indexes is the ability to include a set of column in the index without actually indexing those columns. So an index example for your table might look like this:

CREATE INDEX [ix_MyTable] ON [MyTable] 
(
            [Col1] ASC,
            [Col2] ASC
)
INCLUDE ( [Col3])

Now samples 2 and 3 are both covered. Sample 1 is still not covered, because the index is still not useful for the WHERE clause.

Why INCLUDE Col3, rather than just listing it with the others? It's important to remember that as you add indexes or make them more complex, operations that change data using those indexes will require more and more work, because each change will also require updating the indexes. If you include a column in an index, without actually indexing it, an update to that column still needs to go back and update the index as well, so that the data in the index is accurate... but it doesn't also need to re-order the index based on the new value. So this saves some work for our database server. To put it another way, if a column will only be in the select list, and not in the where clause, you might get a small performance benefit by including it in an index to get the benefit of covering a query from the index, without actually indexing on the column.

score 0 · Answer 2 · answered Oct 01 '13 at 13:53

It is not just the where clause and select clause. A group by clause also needs its columns to be covered by the index for it to be a covering index. Basically, to be a covering index, it needs to contain all the column used in the query for a given table. However, if you don't include them in the right order, the index won't be used.

If the column order in the index is (col1, col2, col3), then the index can't be used for query one since you are selecting by col3. Think of it like a phone book sorted by last name, then first name, then middle initial. Finding everyone with a last name Smith is easy, finding everyone with the first name John isn't helped by the sorting, you have to read the whole phone book. Same for the index. Finding a col1 value is easy. Finding a col1 value and then col2 values is fine. Just finding col3 or just col2 is not helped by the index.

thanks - I didnt think of the group by clause! I was aware of the column order - will make note of the group by! — user2796381, Oct 01 '13 at 13:55

Database covering a query

2 Answers2