We are having a lot of learning and fun working on the complex project for one of our prestige clients. As we develop we learn new things everyday on Magento 2 and always feel to share our learning with the community because without giving you don’t get anything back as we all know sharing is caring ????
Today we are going to share how to create indexes on the existing Magento 2 or custom tables. Table indexes are good in many ways if you know what you are doing for example you can improve the performance of your queries by creating indexes on the fields which are getting used all the time in your where clauses. Other example is unique index which can guarantee uniquely identifiable records in the table. But make sure you are not creating too many indexes because they could also slow down your insert queries and could have bigger impact in performance instead of any gain.
Anyways lets crack on with creating indexes in Magento 2, we are assuming you already have your module with tables in Magento 2 DB.
We will start with creating unique index on custom table with multiple fields by using our ScommerceMageCustomSetupUpgradeSchema.php file
namespace ScommerceMageCustomSetup; use MagentoFrameworkDBDdlTable;
use MagentoFrameworkSetupModuleContextInterface;
use MagentoFrameworkSetupUpgradeSchemaInterface;
use MagentoFrameworkSetupSchemaSetupInterface; class UpgradeSchema implements UpgradeSchemaInterface
{ public function upgrade(SchemaSetupInterface $setup, ModuleContextInterface $context) { $setup->startSetup(); if(version_compare($context->getVersion(), '1.0.1', '<')) { $setup->getConnection()->addIndex( $setup->getTable('custom_table'), 'unique_index_name', array('custom_field_1', 'custom_field_2', 'custom_field_2'), MagentoFrameworkDBAdapterAdapterInterface::INDEX_TYPE_UNIQUE ); } $setup->endSetup(); }
}
The following code will create unique index on custom table with single field
namespace ScommerceMageCustomSetup; use MagentoFrameworkDBDdlTable;
use MagentoFrameworkSetupModuleContextInterface;
use MagentoFrameworkSetupUpgradeSchemaInterface;
use MagentoFrameworkSetupSchemaSetupInterface; class UpgradeSchema implements UpgradeSchemaInterface
{ public function upgrade(SchemaSetupInterface $setup, ModuleContextInterface $context) { $setup->startSetup(); if(version_compare($context->getVersion(), '1.0.1', '<')) { $setup->getConnection()->addIndex( $setup->getTable('custom_table'), 'unique_index_name', 'custom_field_1', MagentoFrameworkDBAdapterAdapterInterface::INDEX_TYPE_UNIQUE ); } $setup->endSetup(); }
}
The following code will create an index on custom table with multiple fields
namespace ScommerceMageCustomSetup; use MagentoFrameworkDBDdlTable;
use MagentoFrameworkSetupModuleContextInterface;
use MagentoFrameworkSetupUpgradeSchemaInterface;
use MagentoFrameworkSetupSchemaSetupInterface; class UpgradeSchema implements UpgradeSchemaInterface
{ public function upgrade(SchemaSetupInterface $setup, ModuleContextInterface $context) { $setup->startSetup(); if(version_compare($context->getVersion(), '1.0.1', '<')) { $setup->getConnection()->addIndex( $setup->getTable('custom_table'), 'index_name', array('custom_field_1', 'custom_field_2', 'custom_field_2') ); } $setup->endSetup(); }
}
The following code will create an index on custom table with single field
namespace ScommerceMageCustomSetup; use MagentoFrameworkDBDdlTable;
use MagentoFrameworkSetupModuleContextInterface;
use MagentoFrameworkSetupUpgradeSchemaInterface;
use MagentoFrameworkSetupSchemaSetupInterface; class UpgradeSchema implements UpgradeSchemaInterface
{ public function upgrade(SchemaSetupInterface $setup, ModuleContextInterface $context) { $setup->startSetup(); if(version_compare($context->getVersion(), '1.0.1', '<')) { $setup->getConnection()->addIndex( $setup->getTable('custom_table'), 'index_name', 'custom_field_1' ); } $setup->endSetup(); }
}
That’s it, Hope this article helped you in some way. Please leave us your comment and let us know what do you think? Thanks.
