Why are all fields in the generated tmp tables TEXT? Greatly improved performance using varchar(255) for at least code & parent

[pepijnblom]

We are importing hundreds of thousands of products and have run into the issue that there are joins being done between tmp_akeneo_connector_entities_product and tmp_akeneo_connector_entities_product_model and specifically on the fields code and parent. Is there any reason those fields need to be TEXT? They are usually used to store EAN I think and I doubt anyone needs EANs that long

This is causing the import to run for many many hours.

Adding a crude composer patch so that code and parent are stored as varchar(255) and adding an index has cut this time down to less than an hour.

Index: vendor/akeneo/module-magento2-connector-community/Helper/Import/Entities.php
IDEA additional info:
Subsystem: com.intellij.openapi.diff.impl.patch.CharsetEP
<+>UTF-8
===================================================================
diff --git a/vendor/akeneo/module-magento2-connector-community/Helper/Import/Entities.php b/vendor/akeneo/module-magento2-connector-community/Helper/Import/Entities.php
--- a/vendor/akeneo/module-magento2-connector-community/Helper/Import/Entities.php
+++ b/vendor/akeneo/module-magento2-connector-community/Helper/Import/Entities.php  (date 1676651721213)
@@ -257,15 +257,24 @@

         /** @var string $field */
         foreach ($fields as $field) {
-            if ($field) {
-                /** @var string $column */
-                $column = $this->formatColumn($field);
-                $table->addColumn(
-                    $column,
-                    Table::TYPE_TEXT,
-                    $this->getAttributeColumnLength($family, $column),
-                    [],
-                    $column
+            if (!$field) {
+                continue;
+            }
+            $column = $this->formatColumn($field);
+            // Setting TYPE_TEXT with length 255 creates a varchar in Magento 2
+            $length = 'code' === $column || 'parent' === $column ? 255 : $this->getAttributeColumnLength($family, $column);
+            $table->addColumn(
+                $column,
+                Table::TYPE_TEXT,
+                $length,
+                [],
+                $column
+            );
+            if ('code' === $column || 'parent' === $column) {
+                $table->addIndex(
+                    'TMP_INDEX_COLUMN_' . strtoupper($column),
+                    $column,
+                    ['type' => AdapterInterface::INDEX_TYPE_INDEX]
                 );
             }
         }