How to increase performance of Magento Enterprise in ~ 100 times

windows
performance
enterprise-edition

(Dmitry Fedyuk) #1

Replace the Magento\Framework\ForeignKey\Config\DbReader::getDbConstraints() method in the vendor/magento/framework-foreign-key/Config/DbReader.php file with the following one:

    /**
	 * 2018-08-15 Dmitry Fedyuk https://www.upwork.com/fl/mage2pro
	 * How to increase performance of Magento Enteprise in ~ 100 times:
	 * https://mage2.pro/t/5680
	 *
     * Retrieve constraints that are declared in given database
     *
     * @param string $connectionName
     * @param array $connectionConfig
     * @return array
     */
    private function getDbConstraints($connectionName, array $connectionConfig)
    {
    	/** @var \Magento\Framework\DB\Adapter\AdapterInterface|\Magento\Framework\DB\Adapter\Pdo\Mysql $connection */
		$connection = $this->connectionFactory->create($connectionConfig);
        $constraints = [];

        if ($connection instanceof \Magento\Framework\DB\Adapter\Pdo\Mysql) {
            foreach ($connection->listTables() as $tableName) {
                $foreignKeys = $connection->getForeignKeys($tableName);

                foreach ($foreignKeys as $foreignKey) {
                    $row = [
                        'name' => $foreignKey['FK_NAME'],
                        'table_name' => $foreignKey['TABLE_NAME'],
                        'reference_table_name' => $foreignKey['REF_TABLE_NAME'],
                        'field_name' => $foreignKey['COLUMN_NAME'],
                        'reference_field_name' => $foreignKey['REF_COLUMN_NAME'],
                        'delete_strategy' => $foreignKey['ON_DELETE'],
                    ];
                    $row['connection'] = $connectionName;
                    $row['reference_connection'] = $connectionName;
                    $row['delete_strategy'] = 'DB ' . $row['delete_strategy'];
                    $key = $row['table_name']. $row['reference_table_name'] . $row['field_name'] . $row['reference_field_name'];
                    $constraintId = sha1($key);
                    $constraints[$constraintId] = $row;
                }
            }
        } else {
			$select = $connection->select()
				->from(['info' => 'information_schema.KEY_COLUMN_USAGE'], [])
				->joinInner(
					['constraints' => 'information_schema.REFERENTIAL_CONSTRAINTS'],
					'constraints.CONSTRAINT_NAME = info.CONSTRAINT_NAME'
					. ' AND constraints.CONSTRAINT_SCHEMA = info.CONSTRAINT_SCHEMA',
					[]
				)->where(
					'info.REFERENCED_TABLE_NAME IS NOT NULL'
				)->where(
					'info.CONSTRAINT_SCHEMA = ?',
					$connectionConfig['dbname']
				)->columns(
					[
						'name' => 'constraints.CONSTRAINT_NAME',
						'table_name' => 'info.TABLE_NAME',
						'reference_table_name' => 'info.REFERENCED_TABLE_NAME',
						'field_name' => 'info.COLUMN_NAME',
						'reference_field_name' => 'info.REFERENCED_COLUMN_NAME',
						'delete_strategy' => 'constraints.DELETE_RULE'
					]
				);
			$rawData = $connection->fetchAssoc($select);
			$constraints = [];
			foreach ($rawData as $row) {
				$row['connection'] = $connectionName;
				$row['reference_connection'] = $connectionName;
				$row['delete_strategy'] = 'DB ' . $row['delete_strategy'];
				$key = $row['table_name']. $row['reference_table_name'] . $row['field_name'] . $row['reference_field_name'];
				$constraintId = sha1($key);
				$constraints[$constraintId] = $row;
			}
		}
        return $constraints;
    }