Custom MetaFields and Database Indexes

Vote:
 

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?

#199533
Edited, Nov 29, 2018 16:03
Vote:
 

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/

#199535
Nov 29, 2018 16:34
Vote:
 

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? 

#199563
Nov 30, 2018 15:01
Vote:
 

No there is not. You would have to create the index via SQL Server directly yourself 

#199582
Dec 01, 2018 12:09
This topic was created over six months ago and has been resolved. If you have a similar question, please create a new topic and refer to this one.
* You are NOT allowed to include any hyperlinks in the post because your account hasn't associated to your company. User profile should be updated.