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.
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)
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 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?