Add index to orchard system table

[Tommy-yuan]

We found that if we are using orchard component such as widgets or users on page, when the page is rendering, multiple sql query will be executed(eg. find certain component in content item record table and content type table, then join content version table to decide whether it's published, etc ), which is very consuming on database if high concurrency occurs . After we have added index on these orchard system tables, the performance is almost five times better than the situation without adding index. So we wonder why orchard CMS don't add index to these system tables when initiate the website, or we are missing some configuration?

[BenedekFarkas]

We already have an ongoing discussion under #5159. What indices would you recommend adding? There are a few that we added recently btw (IdentityPartRecord.Identity, UserPartRecord.NormalizedUserName, CommonPart(Version)Record date times).

[BenedekFarkas]

@Tommy-yuan please comment your suggestions, if any, under #5159.