SQL Statement - How can Improve speed with indexing

Question

I have a script that has to look throught over 2.5 million records to find if a member that has an unread email. I want to know what can be done to improve its speed. Currently it can take up to 8 seconds to run the script:

SELECT TOP(1) MemberMailID
FROM MemberMail
WHERE ToReadFlag = 0
AND ToMemberID = 102
AND ToDeletedFlag = 0
AND FromDeletedFlag = 0
AND OnHold = 0
AND ToArchivedFlag = 0

How could I make it faster using indexes?

Answer 1

This index will probably be helpful, but keep in mind that there is no free lunch (indexes have to be maintained, so this will affect your insert/update/delete workload):

CREATE NONCLUSTERED INDEX unread_emails
  ON dbo.MemberMail(ToMemberID)
  INCLUDE (MemberMailID)
  WHERE ToReadFlag = 0
  AND ToDeletedFlag = 0
  AND FromDeletedFlag = 0
  AND OnHold = 0
  AND ToArchivedFlag = 0;

Now your query can say:

SELECT TOP (1) MemberMailID
  FROM dbo.MemberMail -- dbo prefix
    WITH (INDEX (unread_emails)) -- in case you need to force, though you should not
WHERE ToMemberID = 102
AND ToReadFlag = 0
AND ToDeletedFlag = 0
AND FromDeletedFlag = 0
AND OnHold = 0
AND ToArchivedFlag = 0
ORDER BY ToMemberID; -- ORDER BY is important!

If you change the values of some of these flags depending on the query, you may experiment with adding those columns to the key of the index instead of the filter, e.g. let's say sometimes you check for OnHold = 0 and sometimes OnHold = 1:

CREATE NONCLUSTERED INDEX unread_emails
  ON dbo.MemberMail(ToMemberID, OnHold)
  INCLUDE (MemberMailID)
  WHERE ToReadFlag = 0
  AND ToDeletedFlag = 0
  AND FromDeletedFlag = 0
  AND ToArchivedFlag = 0;

You may also want to experiment with having MemberMailID in the key instead of the INCLUDE. e.g.:

CREATE NONCLUSTERED INDEX unread_emails
  ON dbo.MemberMail(ToMemberID, MemberMailID)
  WHERE ToReadFlag = 0
  AND ToDeletedFlag = 0
  AND FromDeletedFlag = 0
  AND OnHold = 0
  AND ToArchivedFlag = 0;

These differences may not matter for your data and usage patterns, but you'll be able to test differences easier than we'll be able to guess.