Getting category name for each product in Magento with SQL only. Is this query slow?

Getting category name for each product in Magento with SQL only. Is this query slow?

August 17, 2026 · Illustrated author avatarBy Stefan Bogdanescu
Getting category name for each product in Magento with SQL only. Is this query slow?

Title: Optimizing Magento Product Category Query Performance

Dear Developer,

You’ve posed an important question about optimizing your SQL query for exporting product names, IDs, category names, and other attributes from the Magento database. Let me break down your current approach and provide some insights to improve performance.

Current Approach Analysis

Your existing query is a good starting point:

select pr.entity_id as product_id
     , pr.name as product_name
     , catn.name as category_name 
  from catalog_product_flat_1 as pr
  left join catalog_category_product as cat on pr.entity_id = cat.product_id
  left join catalog_category_flat_store_1 as catn on catn.entity_id = cat.category_id
 order by pr.entity_id ASC;

You’re using the flat tables (catalog_product_flat_1 and catalog_category_flat_store_1), which are optimized for read operations. The main advantage is that they pre-aggregate data, reducing join complexity.

However, there are a few potential areas to optimize:

Indexing Considerations

Ensure your tables have proper indexing. For the entity_id field in both catalog_product_flat_1 and catalog_category_flat_store_1, you should have an index to speed up joins. You can verify this with:

SHOW INDEX FROM catalog_product_flat_1;

If indexes are missing or need optimization, you can add them using the CREATE INDEX statement.

Query Performance Benchmarking

To measure the performance of your current query, you can use the EXPLAIN SELECT command in MySQL. This will provide information about how the database intends to execute the query:

EXPLAIN SELECT * FROM (YourQuery);

Look for key metrics like “key” and “ref”. A higher number of keys used and a lower value for “ref” generally indicate better performance.

Flat Tables vs Main Tables

The stackoverflow answer you mentioned suggests using main tables instead of flat tables. While this could potentially reduce the need for joins (if you have enough memory), it introduces additional complexity in terms of joins required (4 left joins).

Flat tables are designed to optimize read-heavy operations, whereas main tables are optimized for write operations and require more joins but provide better data integrity.

Conclusion

Based on your scenario with 440k products, I recommend:

  1. Ensuring proper indexing on the entity_id fields.
  2. Using EXPLAIN SELECT to benchmark performance.
  3. Keeping the use of flat tables for now, as they are optimized for read operations.

However, if you find that performance is still not satisfactory and your server has ample memory, it might be worth exploring denormalization techniques or using Magento’s built-in EAV (Entity-Attribute-Value) model to flatten data in a more controlled manner. But this would require careful architectural consideration.

Remember, optimizing database queries involves a balance between read performance, write performance, and data integrity. Always test changes thoroughly before deploying them to a production environment.

For more advanced Magento performance optimization techniques, I’d recommend checking out Laravel Company’s guide on Magento Performance Tuning. They provide in-depth insights into how to optimize your Magento store for maximum speed and efficiency.

I hope this helps! Let me know if you have any other questions.

Catalog Indexing Database Performance

Our network