Add INDEX to Magento 2 tables?

Question

My Sysadmin says there's some space for optimization of DB by adding INDEX to some tables.

Namely:

catalog_product_entity
catalog_product_entity_varchar

Has anybody tried this before? Any guidance on how to achieve this?

I have not seen any articles on this.

I'm mostly a front-end DEV so this kind of stuff is new to me.

UPDATE:

I tested:

ALTER TABLE `catalog_product_entity_varchar` ADD INDEX `catalog_product_entity_varchar_id_index` (`value_id`)

It seems to have reduced the product page timing by about 0.26083159446725 on a local machine.

BEFORE: 0.82381319999695
AFTER: 0.56298160552979

Does anybody know what the effects of this are on general store management? Indexing, errors, and so on?

UPDATE:

Hmmm, it does not make any sense - the table already has indexed for each of the columns.

Going to retest with the backup of the DB.

Answer 1

I re-checked a couple more big tables and they all had indexed columns.

I still went on and created extra indexes for them to see if it helped, but it looks like it made the situation worse.

Comparing the DB with and without new indexes, the added indexes worsened the lookups:

OLD DB (WITHOUT ADDITIONAL INDEXES):

NEW DB (WITH ADDITIONAL INDEXES):

I don't know why the on-page results were different from query lookups via MySQL(must be some sort of caching).

It looks like I'll just have to optimize the containers that are causing these big lookups.

Answer 2

You can create your column as an index key for better performance and speedy retrieval of data from a table. An index is a performance optimization feature that enables data to be accessed faster compared to other non-index columns. An index is used to more quickly find rows in the table based on the values which are part of the index and they don’t define the uniqueness of a column. You can add your column as Index key using Magento 2 by below way using InstallSchema or UpgradeSchema.php file,

->addIndex(
    $installer->getIdxName('table_name', ['table_field']),
    ['table_field']
)

Here table_name is your table name of database and table_field in your field in which you want to create an index key. You can check the blog for Add foreign key to table, Add Foreign key to the table using Magento 2.