Setting up 1C-Bitrix database indexes

Database Index Configuration in 1C-Bitrix

A query to a catalog of 50,000 products takes 4 seconds instead of 40 milliseconds. EXPLAIN shows ALL instead of ref — the table is read entirely. This is typical scenario for Bitrix site under load, where indexes weren't added after data growth or were deleted on schema update.

Key Bitrix Tables and Their Indexes

Data storage structure of infoblocks is split across multiple tables:

• b_iblock_element — main element records (fields ACTIVE, IBLOCK_ID, CODE, XML_ID, DATE_ACTIVE_FROM)
• b_iblock_element_property — property values of string/number type
• b_iblock_property_enum — list property values
• b_iblock_section — sections (catalog categories)
• b_iblock_section_element — element-section relationship

On production project b_iblock_element_property easily reaches 10–30 million rows. Filtering query by two properties without index — full scan of both tables.

Diagnosing Slow Queries

Enable slow query log in MySQL/MariaDB:

SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 1;
SET GLOBAL slow_query_log_file = '/var/log/mysql/slow.log';

Bitrix supports its own profiling via constant in dbconn.php:

define("DBDebug", true);
define("DBDebugToFile", true);

Log writes to bitrix/modules/main/tools/bx_sql.log. But on production enable briefly — file grows instantly.

Mandatory Indexes for Infoblocks

Check index presence:

SHOW INDEX FROM b_iblock_element;
SHOW INDEX FROM b_iblock_element_property;

If missing composite index on (IBLOCK_ID, ACTIVE, DATE_ACTIVE_FROM) in b_iblock_element — add:

ALTER TABLE b_iblock_element
ADD INDEX ix_ie_iblock_active_date (IBLOCK_ID, ACTIVE, DATE_ACTIVE_FROM);

For property filtering via CIBlockElement::GetList with PROPERTY_* parameter, critical index in b_iblock_element_property:

ALTER TABLE b_iblock_element_property
ADD INDEX ix_iep_iblock_prop_val (IBLOCK_ID, IBLOCK_PROPERTY_ID, VALUE);

ALTER TABLE b_iblock_element_property
ADD INDEX ix_iep_element_prop (IBLOCK_ELEMENT_ID, IBLOCK_PROPERTY_ID);

Indexes for Search and Hit Modules

The b_search_content table grows quickly with active search. Basic index:

ALTER TABLE b_search_content
ADD INDEX ix_sc_module_item (MODULE_ID, ITEM_ID);

The b_stat_phrase_date table (module statistic) — common reason for slow reports:

ALTER TABLE b_stat_phrase_date
ADD INDEX ix_spd_date_phrase (DATE1, PHRASE_ID);

Indexes for Catalog Module

The catalog module works with b_catalog_price, b_catalog_product, b_catalog_store_product tables:

-- Search by price range
ALTER TABLE b_catalog_price
ADD INDEX ix_cp_catalog_price (CATALOG_GROUP_ID, PRICE, CURRENCY);

-- Stock levels on warehouses
ALTER TABLE b_catalog_store_product
ADD INDEX ix_csp_product_store (PRODUCT_ID, STORE_ID);

Without second index, stock query across all warehouses on catalog listing — O(n) on warehouse stock table.

Statistics and Cleanup of Outdated Data

Bitrix sites with enabled statistic module accumulate millions of rows in b_stat_* tables. Separate problem — index fragmentation. After bulk deletes via "Clear Statistics" in admin panel, indexes don't rebuild automatically.

Analyze fragmentation:

SELECT TABLE_NAME,
       ROUND(DATA_FREE/1024/1024, 2) AS free_mb,
       ROUND(DATA_LENGTH/1024/1024, 2) AS data_mb
FROM information_schema.TABLES
WHERE TABLE_SCHEMA = 'bitrix_db'
  AND DATA_FREE > 10485760
ORDER BY DATA_FREE DESC;

Rebuild indexes without locking (MySQL 5.6+):

ALTER TABLE b_iblock_element_property ENGINE=InnoDB;
-- or
OPTIMIZE TABLE b_iblock_element_property;

OPTIMIZE TABLE on InnoDB actually does ALTER TABLE ... ENGINE=InnoDB — full rebuild. On 5 GB table takes 10–20 minutes with locking. For online rebuild — pt-online-schema-change from Percona Toolkit.

Automatic Index Maintenance

Add to /bitrix/php_interface/init.php agent running ANALYZE TABLE weekly on critical tables:

CAgent::AddAgent(
    'MyAnalyzeTables();',
    'main',
    'N',
    604800, // weekly
    '',
    'Y'
);

function MyAnalyzeTables() {
    global $DB;
    $tables = ['b_iblock_element', 'b_iblock_element_property', 'b_catalog_price'];
    foreach ($tables as $t) {
        $DB->Query("ANALYZE TABLE {$t}");
    }
    return 'MyAnalyzeTables();';
}

ANALYZE TABLE updates statistics without locking on InnoDB — query optimizer gets current data on value distribution.