Which MySQL database tables contain the data for the stores in Magento 1.x?

Which MySQL database tables contain the data for the stores in Magento 1.x?

October 3, 2026 · Illustrated author avatarBy Stefan Bogdanescu
Which MySQL database tables contain the data for the stores in Magento 1.x?

Retrieving Magento Store Data via MySQL for Magento 1.x

When you need to retrieve various details about your stores in a Magento 1.x installation using MySQL, it’s essential to understand the database schema containing this information. This guide will walk you through identifying the required tables and fields to gather all necessary data efficiently.

Identifying Relevant Tables in the Magento Database

The core Magento database is organized around several key tables. For store-specific data, focus on these three primary tables:

  1. core_store
  2. core_config_data
  3. page_layout

The core_store Table

This table stores basic information about each store. To retrieve the active stores along with their package, default theme, and other details, query this table using the following SQL statement:

SELECT * FROM core_store WHERE status = 1;

In the result set, you’ll find columns like store_id, code, name, sort_order, etc. The code field corresponds to the store’s unique identifier.

The core_config_data Table

This table holds configuration settings for your Magento installation, including theme information. To retrieve the magento default theme and the store default theme for each active store:

SELECT * FROM core_config_data WHERE config_id IN (
    SELECT main_table.config_id
    FROM (
        SELECT config_id
        FROM core_config_data
        WHERE path = 'design/head/default_head' AND scope = 0
    ) AS main_table
);

The page_layout Table

This table stores the layout information for each store. To fetch the store layout details:

SELECT * FROM page_layout;

Optimizing Your MySQL Queries

When dealing with large Magento installations, it’s crucial to optimize your queries to avoid exceeding memory limits.

  1. Limit the Result Set: Add LIMIT clauses to reduce the number of returned rows if possible.

  2. Indexed Columns: Ensure that the columns used in your WHERE conditions are indexed for faster query execution.

  3. Use Appropriate Data Types: When dealing with textual data, store it in appropriate character sets and collations optimized for your workload.

Building Your PHP Data Structure

To build a structured representation of your stores in PHP, consider using arrays or objects to hold the retrieved data:

<?php

$stores = [
    (object)[
        'store_id' => 1,
        'code' => 'main_site',
        'name' => 'Main Store',
        'theme_magento_default' => 'default/default',
        'theme_store_default' => 'default/default',
        'layout' => '2columns-left',
    ],
    // More store data...
];

Conclusion

Successfully retrieving Magento 1.x store information via MySQL involves understanding the relevant tables and optimizing your queries for performance. By leveraging the core_store, core_config_data, and page_layout tables, you can efficiently gather all required details to build your PHP data structure.

Remember that proper indexing and query optimization are essential when dealing with large datasets in Magento. If you find yourself facing memory limits or performance issues, consider profiling your queries using MySQL’s EXPLAIN statement and optimizing your database design accordingly.

For more advanced techniques in Magento development, be sure to explore the insights shared by our team of experts at Laravel Company. We regularly publish articles on Magento best practices, performance optimizations, and custom development strategies to help you unlock the full potential of your e-commerce platform.

Magento 1 Database Themes Configuration

Our network