SQL | The Magento Expert

Finding the Largest Database Tables Using SQL (Magento 2)

Mex 16. Oktober 2026 Magento Solutions

A Magento database can grow large over time. Before you investigate or delete individual tables, identify which tables consume the most space. The following SQL query lists the 20 largest tables, including data size, index size and total size:

SELECT
    TABLE_NAME AS Table_Name,
    TABLE_ROWS AS Rows_Estimated,
    ROUND(DATA_LENGTH / 1024 / 1024, 2) AS Data_MB,
    ROUND(INDEX_LENGTH / 1024 / 1024, 2) AS Index_MB,
    ROUND((DATA_LENGTH + INDEX_LENGTH) / 1024 / 1024, 2) AS Total_MB,
    ROUND(DATA_LENGTH / 1024 / 1024 / 1024, 3) AS Data_GB,
    ROUND(INDEX_LENGTH / 1024 / 1024 / 1024, 3) AS Index_GB,
    ROUND((DATA_LENGTH + INDEX_LENGTH) / 1024 / 1024 / 1024, 3) AS Total_GB
FROM information_schema.TABLES
WHERE TABLE_SCHEMA = 'YOUR_MAGENTO_DATABASE'
ORDER BY (DATA_LENGTH + INDEX_LENGTH) DESC
LIMIT 20;

SELECT

TABLE_NAME AS Table_Name,

TABLE_ROWS AS Rows_Estimated,

ROUND(DATA_LENGTH / 1024 / 1024, 2) AS Data_MB,

ROUND(INDEX_LENGTH / 1024 / 1024, 2) AS Index_MB,

ROUND((DATA_LENGTH + INDEX_LENGTH) / 1024 / 1024, 2) AS Total_MB,

ROUND(DATA_LENGTH / 1024 / 1024 / 1024, 3) AS Data_GB,

ROUND(INDEX_LENGTH / 1024 / 1024 / 1024, 3) AS Index_GB,

ROUND((DATA_LENGTH + INDEX_LENGTH) / 1024 / 1024 / 1024, 3) AS Total_GB

FROM information_schema.TABLES

WHERE TABLE_SCHEMA = 'YOUR_MAGENTO_DATABASE'

ORDER BY (DATA_LENGTH + INDEX_LENGTH) DESC

LIMIT 20;

…

View solution

Finding Slow Database Queries with the Query Log (Magento 2)

Mex 13. Oktober 2026 Magento Solutions

When a Magento 2 store is slow, PHP, Redis and Varnish are not always to blame. Individual slow database queries can also make certain pages or processes much slower than expected. Magento 2 includes a Query Log that you can…

View solution

Finding products without product images using SQL (Magento 2)

Mex 25. September 2026 Magento Solutions

Sometimes you need to find all products without assigned images in Magento 2, for example to verify that a product import or data migration is complete. The following SQL query returns products without product images:

SELECT
    p.entity_id,
    p.sku,
    p.type_id
FROM catalog_product_entity AS p
LEFT JOIN catalog_product_entity_media_gallery_value_to_entity AS mg
    ON mg.entity_id = p.entity_id
WHERE mg.value_id IS NULL
ORDER BY p.entity_id;

SELECT

p.entity_id,

p.sku,

p.type_id

FROM catalog_product_entity AS p

LEFT JOIN catalog_product_entity_media_gallery_value_to_entity AS mg

ON mg.entity_id = p.entity_id

WHERE mg.value_id IS NULL

ORDER BY p.entity_id;

View solution

Deleting orders directly from the database with SQL (Magento 2)

Mex 11. Februar 2025 Magento Solutions

Deleting large numbers of orders from Magento 2 admin often causes timeouts. You can also delete orders directly through the database. Open phpMyAdmin and run the following two statements: Proceed at your own risk. Test on a staging system or…

View solution

Fixing the Mage_Core_Model_Store::setWebsite() argument type error (Magento 1)

Mex 2. Mai 2016 Magento Solutions

You might encounter the message Argument 1 passed to Mage_Core_Model_Store::setWebsite() must be an instance of Mage_Core_Model_Website, null given when moving your store to another server. The solution is straightforward. Run the following SQL queries to resolve the issue:

SET FOREIGN_KEY_CHECKS=0;
UPDATE core_store_group SET group_id = 0 WHERE name = 'Default';
UPDATE core_store SET store_id = 0 WHERE code = 'admin';
UPDATE core_website SET website_id = 0 WHERE code = 'admin';
UPDATE customer_group SET customer_group_id = 0 WHERE customer_group_code = 'NOT LOGGED IN';
SET FOREIGN_KEY_CHECKS=1;

SET FOREIGN_KEY_CHECKS=0;

UPDATE core_store_group SET group_id = 0 WHERE name = 'Default';

UPDATE core_store SET store_id = 0 WHERE code = 'admin';

UPDATE core_website SET website_id = 0 WHERE code = 'admin';

UPDATE customer_group SET customer_group_id = 0 WHERE customer_group_code = 'NOT LOGGED IN';

SET FOREIGN_KEY_CHECKS=1;

View solution

Deleting all products using SQL (Magento 1)

10. März 2016 Magento Solutions

To delete all product related data, use the following query: DELETE FROM catalog_product_entity; Due to the constraints it will delete all related data from other tables as well.

View solution

Deleting all products using SQL (Magento 1)

Mex 23. Februar 2016 Magento Solutions

Want to remove all products from your Magento store directly through the database? The following statement removes the products and their related data:

DELETE FROM catalog_product_entity;

1	DELETE FROM catalog_product_entity;

Thanks to the constraints on this table, product relationships are also removed from the relevant tables.

View solution

Getting products with or without custom options using SQL (Magento 1)

15. Januar 2016 Magento Solutions

To get all products that do not have custom options, simply run the following SQL query: SELECT * FROM catalog_product_entity WHERE entity_id NOT IN (SELECT DISTINCT product_id FROM catalog_product_option); To get all products that do have custom options, remove the…

View solution

Finding products with and without custom options using SQL (Magento 1)

Mex 15. Januar 2016 Magento Solutions

To find all products that have keine no custom options, run this query:

SELECT
  *
FROM catalog_product_entity
WHERE entity_id NOT IN (SELECT DISTINCT
    product_id
  FROM catalog_product_option);

SELECT

FROM catalog_product_entity

WHERE entity_id NOT IN (SELECT DISTINCT

product_id

FROM catalog_product_option);

To find all products that do have custom options, simply remove “NOT” from the query above.

View solution

Changing the admin password directly in the database using SQL (Magento 1)

Mex 29. Oktober 2015 Magento Solutions

Use the following SQL statement to reset an administrator’s password directly in the database:

UPDATE `admin_user` SET `password` = MD5('NEUESPASSWORT') WHERE `username` = 'ADMINUSERNAME';

1	UPDATE `admin_user` SET `password` = MD5('NEUESPASSWORT') WHERE `username` = 'ADMINUSERNAME';

View solution

« Previous