Non-Clustered Indexes - keys and nonkeys

Question

I just want to make sure I'm on the right track with these concepts, so any feedback would be greatly appreciated.

Here's my theory from the query I've just optimised, through a process of trial and error and reading the MSDN documentation.

The Query

DECLARE @pic_id int
SET pic_id = 1

SELECT ROW_NUMBER() OVER (ORDER BY pic_date desc) AS row_num, *
FROM tbl_pics
WHERE deleted = 0 AND map_id = 1 AND (hidden = 0 OR pic_id = @pic_id)

The Index

CREATE NONCLUSTERED INDEX [IX_tbl_pics] ON [dbo].[tbl_pics] 
(
    [map_id] ASC,
    [deleted] ASC,
    [pic_date] DESC
)
INCLUDE ( [hidden], [pic_id] )

There is also a PK index on pic_id

The Theory

The key columns are so, because they are used in either a WHERE clause (but not used in a OR situation) or and ORDER BY.

The nonkey (INCLUDE) columns as so, because they get used in the WHERE, but because they are used in an OR scenario they can't (can't = won't improve performance) be a key column.

Are these presumptions correct? If not, what am I missing?

Thanks!

Answer 1

You are asking the query optimizer to produce a plan that can answer the query:

SELECT *
FROM tbl_pics
WHERE deleted = 0 AND map_id = 1 AND hidden = 0;

The rest is fluff (including the OR pic_id = @pic_id). This will be a table scan, guaranteed, because of the low selectivity of the predicates involved (I'm sure deleted and hidden are 0/1, and map_ip I doubt it has any significant impact). The only predicate that could save the query is pic_id = @pic_id but by placing it in an OR condition you killed it's chance. No secondary index can help it, realistically. The addition of ROW_NUMBER will ad a sort, most likely, but the real damage is the scan.

This is a lost cause. Come up with realistic requirements.

Answer 2

Is the primary key also the clustered index? If so, there is no reason to INCLUDE (pic_id) because the unique clustered index will already be used as the bookmark in the nonclustered index.

As far as what you are talking about with the OR, that is effectively a second part of the WHERE, but you're just relying on the selectivity of the first to do most of the work (while relying on the INCLUDE to avoid going to the table).

But by having * in there, you may have to go to the table anyway.

On the other hand, I might not be designing my indexes based on a single query unless this query is very heavily used without taking into account more of the load. And still having a look at the execution plan couldn't hurt.