Custom MetaFields and Database Indexes

Jeroen van Lieshout

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

Quan Mai

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

Jeroen van Lieshout

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

Quan Mai

Vote:

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

#199582

Dec 01, 2018 12:09