How to pull all the product id, skus, product names, description in magento using only mysql?

How to pull all the product id, skus, product names, description in magento using only mysql?

October 5, 2026 · Illustrated author avatarBy Stefan Bogdanescu
How to pull all the product id, skus, product names, description in magento using only mysql?

Extracting Product Data From Magento Using MySQL

If you’re looking to extract product data from your Magento database using only SQL queries, you’ve come to the right place. In this post, I’ll guide you through pulling all product IDs (entity_id), SKUs, names (titles), and descriptions efficiently using MySQL.

Understanding Magento’s Database Schema

Before we dive into the queries, let’s quickly review how Magento structures its catalog data:

  • catalog_product_entity: This table stores the core product information like entity_id and sku.
  • catalog_product_entity_text: This table contains localized attributes for text fields like title (product name) and description.

Retrieving All Product Data

To fetch all required product details, you can use the following SQL query:

SELECT e.entity_id,
       e.sku,
       eav.value AS 'title',
       eav2.value AS 'description'
FROM catalog_product_entity e
JOIN catalog_product_entity_varchar eav
  ON e.entity_id = eav.entity_id AND eav.attribute_id = (SELECT attribute_id FROM eav_attribute WHERE attribute_code = 'title')
JOIN catalog_product_entity_varchar eav2
  ON e.entity_id = eav2.entity_id AND eav2.attribute_id = (SELECT attribute_id FROM eav_attribute WHERE attribute_code = 'description');

Here’s a breakdown of this query:

  1. We’re selecting the required columns: entity_id (product ID), sku, and localized title and description.

  2. The main table is catalog_product_entity (e).

  3. For title and description, we join with catalog_product_entity_varchar using the respective attribute IDs.

  4. We fetch the attribute IDs for ‘title’ and ‘description’ using subqueries on the eav_attribute table.

Optimizing Your Queries

While the above query works, it might be slow for large databases due to the subqueries. For better performance:

  1. Always run queries in a production environment when possible. Query performance can vary widely between different database configurations and hardware.

  2. If you’re running this frequently, consider creating views or temporary tables for better cache utilization. You can create a view like this:

CREATE VIEW product_data AS
SELECT e.entity_id,
       e.sku,
       eav.value AS 'title',
       eav2.value AS 'description'
FROM catalog_product_entity e
JOIN catalog_product_entity_varchar eav
  ON e.entity_id = eav.entity_id AND eav.attribute_id = <TITLE_ATTRIBUTE_ID>
JOIN catalog_product_entity_varchar eav2
  ON e.entity_id = eav2.entity_id AND eav2.attribute_id = <DESCRIPTION_ATTRIBUTE_ID>;

Replace <TITLE_ATTRIBUTE_ID> and <DESCRIPTION_ATTRIBUTE_ID> with the actual values from eav_attribute.

Conclusion

In this post, we saw how to efficiently retrieve all necessary product data (ID, SKU, title, description) from a Magento database using MySQL. We discussed the underlying schema, provided the required queries, and shared optimization tips.

Remember:

  • Always test your queries in a safe environment before running them on live databases.
  • If performance is an issue, consider optimizing your database configuration or using Magento’s built-in API for data retrieval.
  • For more advanced data manipulation tasks, check out our guide on working with Magento data.

Happy coding!

Catalog Database

Our network