0

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

3
  • its about 50 million rows, please remeber to ose number that are known
    – nbk
    Commented Apr 18, 2021 at 18:37
  • Best bet is to index just all four, and add one extra column to some of them. 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 Commented Apr 19, 2021 at 0:18
  • Can you provide example queries that are having performance issues, the exact table DDL and your current indexes?
    – HandyD
    Commented Apr 19, 2021 at 5:52

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Browse other questions tagged or ask your own question.