How do I have to import products in Magento using a direct connection to the database

How do I have to import products in Magento using a direct connection to the database

August 3, 2026 · Illustrated author avatarBy Stefan Bogdanescu
How do I have to import products in Magento using a direct connection to the database

Optimizing Magento Product Import: Direct Database Insertion

As an experienced developer, I understand your frustration when dealing with large-scale product imports in Magento. The built-in tools and APIs often fall short due to their overhead and lack of performance optimization. In cases like yours—with over 50,000 records to import—I recommend a direct approach using SQL queries.

Understanding the Challenge

You’ve already tried several methods to optimize your import process:

  1. Splitting files: This technique helps reduce memory usage but doesn’t significantly improve insertion speed due to Magento’s architectural constraints.
  2. API: The API is more flexible but extremely slow for bulk operations, as it involves multiple layers of processing before reaching the database.
  3. Magento classes: While this method allows you to utilize Magento’s built-in data models and relationships, it still incurs a heavy performance penalty due to object creation and validation checks.

Your current approach using Magento classes spends around 5 hours to insert just 2,300 records—a clear indication that the system isn’t optimized for high-volume imports. To resolve this issue, we’ll explore a direct database insertion strategy using MySQL queries.

Direct Database Insertion: The Solution

Step 1: Identify Relevant Tables

The primary tables involved in product creation are:

  • catalog_product_entity
  • catalog_product_entity_int
  • catalog_product_entity_varchar
  • catalog_product_entity_decimal
  • catalog_product_entity_text

Step 2: Prepare Your Data

Ensure your data is properly formatted and escaped to avoid SQL injection attacks. For example, you can use the mysql_real_escape_string function in PHP or Magento’s built-in escaping functions (Mage::helper('core')->escapeHtml()).

Step 3: Construct the Insert Statement

Here’s a sample SQL statement for inserting product data directly into the database:

INSERT INTO catalog_product_entity (
    entity_id,
    attribute_set_id,
    type_id,
    sku,
    name,
    description,
    short_description,
    price,
    weight,
    status,
    visibility,
    tax_class_id,
    created_at
)
VALUES ('1', '9', 'simple', 'SKU-001', 'Product Name',
        '<p>This is an example product.</p>',
        'Short description...', 24.5, 0.5, 1, 3, 2,
        NOW());

Step 4: Bulk Insert with Prepared Statements

For large-scale imports, use prepared statements to avoid SQL injection vulnerabilities and improve performance:

$stmt = $pdo->prepare("INSERT INTO catalog_product_entity (
    entity_id,
    attribute_set_id,
    type_id,
    sku,
    name,
    description,
    short_description,
    price,
    weight,
    status,
    visibility,
    tax_class_id,
    created_at
)
VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, NOW())");

while ($row = $handle->fetch_assoc()) {
    $stmt->execute([
        1, // entity_id
        9, // attribute_set_id
        'simple', // type_id
        $row['sku'],
        $row['name'],
        $row['description'],
        $row['short_description'],
        $row['price'],
        $row['weight'],
        1, // status
        3, // visibility
        2, // tax_class_id
    ]);
}

Conclusion: Optimizing Performance

By using direct SQL queries and prepared statements, you can significantly improve the performance of your product imports. This approach bypasses Magento’s overhead and focuses solely on database operations, which are much faster for high-volume data.

Remember to:

  1. Optimize your database schema by creating indexes on frequently queried columns.
  2. Test your import process in a staging environment before deploying it to production.
  3. Monitor your server’s resource usage during the import to ensure stability.

While this approach eliminates many performance bottlenecks, keep in mind that it requires careful handling of data integrity and relationships between tables. Make sure to update related tables (e.g., catalog_category_product) after inserting products using appropriate methods.

For more advanced techniques like transaction management and concurrent processing, consider utilizing an ETL tool or Magento’s built-in import/export functionality with custom scripting. For detailed guidance on Magento optimization, I recommend exploring the resources available at Laravel Company.

With this direct database insertion approach, you should see a substantial improvement in your product import performance, allowing you to efficiently handle even the largest datasets in your Magento store.

Catalog Database Performance API

Our network