In our Commerce Setup we have some custom fields defined on Organization and Address Objects. We also need to perform queries on those specific columns as they contain unique id's of external systems. We use FiltersElements for that in combination with BusinessManager.List method. However this is causing serious expensive queries on the database (causing high loads and time-outs) as those custom fields are not indexed. When we manually put indexes on those fields in the database the performance increases significantly. I'm wondering if it is possible to let Commerce set indexes on those custom columns by code when we create those custom metadata fields or that we have to custom code ourselves to create indexes?
I'm glad you asked. Yes, if you are querying by your custom metafields, then you should add indexes to them. I talked about it here: https://vimvq1987.com/index-or-no-index-thats-the-question/
Thanks for the input. Is there also a mechanism in EPI which we can use (like some initialization module) to set up the indexes in the database programatically after the custom metafields have been created?
No there is not. You would have to create the index via SQL Server directly yourself