CODEWITHSUNDEEP
sqlsql-server-2008indexing
Mickboe1
Mickboe1

Reputation: 45

what should i index

i'm a complete index dummie, but I've read and watched quite a bit of vids on indexing but i just can't seem to figure it out.

the table looks like the following:

ID          int
ComponentId int
Value       float
TimeStamp   datetime

imagine this database having about 2 million records and i would like to do something like the following:

SELECT Value
FROM Log 
WHERE ComponentId = X 
AND TimeStamp >= SELECT CONVERT(VARCHAR(8),DATEADD(day, -1, getdate()),112)

That should get me all the values for a selected component within the last 24 hours

And im really wondering if i should index this on ComponentId, Timestamp or both

Thanks in advance!

Upvotes: 1

Views: 36

Answers (2)

Giorgi Nakeuri
Giorgi Nakeuri

Reputation: 35780

I would suggest you to create covering index on the table like:

CREATE UNIQUE NONCLUSTERED INDEX IDX_IndexName ON dbo.TableName(ComponentId, TimeStamp) 
INCLUDE(Value)

This should improve the performance of your query with index seek. Also Value will be stored in the leafs of the index so you get rid of lookups as data that you are selecting is already in your index.

Upvotes: 1

Indra Prakash Tiwari
Indra Prakash Tiwari

Reputation: 1057

you should add composite along with covering index using with ComponentId, Timestamp.

create index ix_Log_ComponentId_Timestamp on log(ComponentId, Timestamp) include (value)

Also, no need to add select before CONVERT(VARCHAR(8),DATEADD(day, -1, getdate()),112)

Upvotes: 2

Related Questions