SQL Indexes, how to create better-CodePudding

I use SQL Azure and have a table with 1M records. Table has columns (main):

Id - int, autoincrement identity

IMEI - unique index

Sync1 - bool, not nullable

Sync2 - bool, not nullable

From these 1M records only approx 6K records have at least Sync1 == true or Sync2 == true (or both). Most of queries are find records with at least one Sync is true

How to create indexes effectively? I see the following:

(Id Sync1), (Id Sync2)
(Id Symc1), (Id Sync2), (Id Sync1 Sync2)
(IMEI Sync1), (IMEI Sync2)

something else?

Thank you!

CodePudding user response：

two indexing ideas to try:

1) Index each of the sync fields separately:

CREATE NONCLUSTERED INDEX IX_MyTable_Sync1 ON  MyTable 
    (Sync1)  
    INCLUDE (IMEI, Sync2, FieldA, FieldB, FieldC); 
CREATE NONCLUSTERED INDEX IX_MyTable_Sync2 ON  MyTable 
    (Sync2)  
    INCLUDE (IMEI, Sync1, FieldA, FieldB, FieldC);

In this case the query should be written as a Union so each index can be used effectively:

Select * from MyTable where Sync1 is not null
UNION
Select * from MyTable where Sync2 is not null

2) A filtered index on Sync1 and Sync2:

CREATE NONCLUSTERED INDEX IX_SyncNotNull ON  MyTable 
    (IMEI, Sync1, Sync2, FieldA, FieldB, FieldC)  
    WHERE (Sync1 IS NOT NULL OR Sync2 IS NOT NULL);

But take note: you query will probably need to have this exact query criteria for the index to be used - (Sync1 is not null OR Sync2 is not null) - so have care. I personally don't have a lot of experience with filtered indexes.

In both cases, I would probably include the fields needed in the query to be sure the index is used, but of course go ahead and try it without including other fields and see if the query plan makes use of the index or not. You should read up on "include" in sql server index, as well as using "covering indexes". I just mention this because your question implicitly assumes that you only need to worry about the fields Sync1 and Sync2 - and that may not be true!

Whatever strategy you try, you should always look at the estimated or actual query plans to see if the indexes are being used (you might even see what indexes SQL Server suggests and could try those suggestions too).

It is essential to know a little about how to investigate query plans for you queries - search for some information on this if you are not familiar with sql server query plans (for example, sites like this one: sql server query plans )