Which index will help on the Votes table

0

Please login or register to vote for this query.

(click on this box to dismiss)

Drupal Answers Meta

Q&A about the site for Drupal developers and administrators

-- spool table to temp table
select *
into #Votes
from Votes

-- default index
CREATE UNIQUE CLUSTERED INDEX [UIX_Votes_Id] ON [#Votes](Id ASC)

-- added indexes

-- option 1
/*
CREATE NONCLUSTERED INDEX [IX_Votes_VoteTypeId] 
   ON [#Votes](VoteTypeId ASC) 
*/

-- option 2

CREATE NONCLUSTERED INDEX [IX_Votes_VoteTypeId_Include] 
   ON [#Votes](VoteTypeId ASC, PostId) 
   INCLUDE (CreationDate)

-- option 3
-- CREATE NONCLUSTERED INDEX [IX_Votes_PostId] ON [#Votes](PostId ASC)

-- option 4
/*
CREATE NONCLUSTERED INDEX [IX_Votes_CreationDate] 
    ON [#Votes](CreationDate ASC)
    INCLUDE (votetypeid)
*/

-- option 5
/*
CREATE NONCLUSTERED INDEX [IX_Votes_PostId_VoteTypeId_Include] 
    ON [#Votes](PostId ASC, VoteTypeId ASC) 
    INCLUDE (CreationDate) 
*/
go
set showplan_all on
go

-- common query scenarios


select count(*) [bounties]
from #votes
where votetypeid = 8


select postid, score
from posts p
inner join #votes on postid = p.id
where votetypeid = 9


select count(*) [accepts in date range]
from #votes
where creationdate > '2020-01-01' -- and '2022-04-01'
and votetypeid = 1

Enter Parameters

Options:
Switch to main site
loading Hold tight while we fetch your results