I am new to db design I also read concepts of non cluster indexing and know about the combined non cluster indexing but in my scenario I am having user table with multiple search on column
table column as follows
userName
fatherName
empId
cardNumber
and so on. (above all 4 no one column is PK)
I am having around 50 millions records over here due to large data search take huge time due to that I am thinking to create indexes in table but these all 4 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 4 then it might be creating issue on performance of 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 huge time shall I created all four separate indexes would this work in combined search of empId and cardNumber
If I created separated 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
userName
with any of the others is not likely to be queried so that doesn't need extra columns etc. Look at what users are querying