CODEWITHSUNDEEP
sql-serversql-server-2008indexing
Thomas
Thomas

Reputation: 5998

Proper indexing of a table give the queries listed

I have the following table in SQL Server:

CREATE TABLE [dbo].[Users](
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [Email] [varchar](128) NOT NULL,
    [CreatedAt] [datetime] NOT NULL,
    [SourceId] [int] NOT NULL
PRIMARY KEY CLUSTERED 
(
    [Id] ASC
) WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF,  ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

This table has the potential to grow big. The most frequent queries ran on this table are going to be:

SELECT * FROM Users WHERE Email = '[email protected]'
SELECT * FROM Users WHERE Email = '[email protected]' AND SourceId = some integer
SELECT * FROM Users WHERE CreatedAt BETWEEN '2011-10-01' AND '2011-10-30'
SELECT * FROM Users WHERE CreatedAt BETWEEN '2011-10-01' AND '2011-10-30' AND SourceId = some integer

Currently I have setup the following indexes:

CREATE INDEX IX_Users_Email_SourceId ON Users (Email, SourceId)
CREATE INDEX IX_Users_CreatedAt ON Users (CreatedAt)
CREATE INDEX IX_Users_SourceId ON Users (SourceId)

Are these indexes sufficient for the type of queries listed above? Should I set ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON to OFF if this is going to be a heavily queried table? I am getting a bit confused as to how to setup indexes properly.

Upvotes: 1

Views: 224

Answers (1)

KM.
KM.

Reputation: 103677

I would index them this way:

WHERE Email = '[email protected]'
index: Email
or Email, SourceId

WHERE Email = '[email protected]' AND SourceId = some integer
index: SourceId, Email
or Email, SourceId

WHERE CreatedAt BETWEEN '2011-10-01' AND '2011-10-30'
index: CreatedAt
or CreatedAt, SourceId 

WHERE CreatedAt BETWEEN '2011-10-01' AND '2011-10-30' AND SourceId = some integer
index: SourceId, CreatedAt 
or CreatedAt, SourceId

It is difficult to be completely sure of these, what is the selectivity of SourceId. Depending on that, you could try to cover multiple queries with a single index (the first two could be combined and the last two could be combined). If SourceId is very selective (few rows match each value) I'd like to not combine the indexes and go for max performance by using four indexes.

Also, you don't mention how many INSERTS/UPDATES you expect so it is hard to determine how much the overhead of more/fewer indexes will play out.

Also, your SELECT * makes it impossible to recommend covering indexes, etc.

Upvotes: 1

Related Questions