I am new to database design. I read concepts of nonclustered indexing and know about the combined nonclustered indexing, but in my scenario, I have a user table with multiple columns to search on.
Table columns are as follows:
userName
fatherName
empId
cardNumber
and so on (none of these 4 columns is the PK)
I have around 50 million rows over here due to large data search takea a long time. Due to this I am thinking to create indexes on table but all four of these searches are optional, means possible user can fill all four or may be one or two of them but I was confused if I make all four separate index and one for all four then it might be causing issues on performance. While entering/inserting the data I created even a single index for all 4 column in combined but while I am searching with only card number then it takes a long time. Shall I create all four separate indexes? Would this work in combined search of empId
and cardNumber
.
If I created separate once then what about combined searches? If I create combinations then indices are 15 which obviously not a good way for insertion... lot of confusion I have can anyone help me thanks in advance.