Indexes needed for user advanced filter?

Question

ASP.NET

MS SQL table: tblPets

ID (123456789)
AnimalType (Dog, Cat, Goldfish...)
AnimalName (Rudolf, Ben, Harold...)
CountryCode (US, AU..)
StateCode (CA, NY...)
CityCode (AK, LA...)
IsMammal (True, False)
IsFish (True, False)
HasFur (True, False)
Color (Black, Brown, Orange...)
WeightKG (34, 57, 18...)

We are thinking of creating an "Advanced Filter" webpage for users to search/filter for all records that match their user-inputted criteria.

We are considering having a filter popup dialog with 10 text input fields allowing the user to filter on one or more criteria.

E.g.:

CountryCode: US
StateCode: CA
IsFish: True
Color: Orange

Assuming we want the table tblPets as indexed as possible for fastest results, what would the indexes be? E.g., would there need to be 100 indexes (10 x 10)?

Assuming column one is named c1, column two c2 etc:

Index1: c1,c2,c3,c4,c5,c6,c7,c8,c9,c10

Index2: c2,c3,c4,c5,c6,c7,c8,c9,c10

Index3: c3,c4,c5,c6,c7,c8,c9,c10

Index4: c4,c5,c6,c7,c8,c9,c10

Index5: c5,c6,c7,c8,c9,c10

Index6: c6,c7,c8,c9,c10

Index7: c7,c8,c9,c10

Index8: c8,c9,c10

Index9: c9,c10

Index10: c10

Index1B: c1,c3,c4,c5,c6,c7,c8,c9,c10

Index2B: c1,c4,c5,c6,c7,c8,c9,c10

Index3B: c1,c5,c6,c7,c8,c9,c10

Index4B: c1,c6,c7,c8,c9,c10

Index5B: c1,c7,c8,c9,c10

Index6B: c1,c8,c9,c10

Index7B: c1,c9,c10

Index8B: c1,c10

Index1C: c2,c4,c5,c6,c7,c8,c9,c10

Etc

You only need a index on each single column. The query plan and data engine will use all of these index(s) to retrieve data, and there is NOT a requirement to build or make so called "compound" indexes that each have multiple columns. In other words, a simple index for each column based on ONE column will suffice in 99% of cases for good performance. I would NOT suggest indexing the bit columns, as such they result into too many results in a given index (only 2 choices results in two VERY long "lists", and they wind up being a type of sequential search. So, don't index the bit (true/false). — Albert D. Kallal, Mar 09 '23 at 12:53
Suggested reading: [Dynamic Search Conditions in T‑SQL](https://www.sommarskog.se/dyn-search-2008.html). Indexing is non-trivial. In general a simple query will only use a single index. — HABO, Mar 10 '23 at 20:45

score 1 · Answer 1 · answered Mar 09 '23 at 08:21

1

Yes, you should put indexes on each column that will be used in the where clause. Pay attention that the indexes means slower write/update performance. The indexes will also increase your table size.

answered Mar 09 '23 at 08:21

D A

1,724
1
8
19

Thanks. So if I have 10 searchable table columns, how many indexes would that be? – user1946932 Mar 09 '23 at 08:26
10. You only need to place a index on each column. You don't need compound indexs here comprised of multiple columns. Such "compound" index's can be used in some cases, but doubtful you need as such. So, create 10 indexes - one for each column. The query planner should then automatic retrieve the data in a reasonable way speed wise here. – Albert D. Kallal Mar 09 '23 at 22:08

score 0 · Answer 2 · answered Mar 09 '23 at 08:35

In this case the best way is to create a CLUSTERED COLUMNSTORE index on the table.

If the table has already a CLUTERED index (usually PRIMARY KEY) dropt the PK constraint and create a new PK with the same columns in a NONCLUSTERED inhdex :

ALTER TABLE ??? DROP CONSTRAINT PK_???;

ALTER TABLE ??? ADD PRIMARY KEY (???) NONCLUSTERED;

Then create the CLUSTERED COLUMNSTORE index :

CREATE CLUSTERED COLUMNSTORE INDEX ON ???;

Indexes needed for user advanced filter?

2 Answers2