This is wonderful question and it applies to all database developers.
- If you see in any database, all the databases(RDBMS) supports only one primary index. So no database allows more than one primary index. But, you can have more than one secondary index.
- The optimal limit for secondary index is 5
- More than 5 secondary indexes, reduces performance of a SQL query on that table
I will explain in my next post, how to decide optimum number of secondary indexes.